Wie man Linq schneller abfragt ⇐ C#

[Anonymous]

NBLINKSEXPIRATIONSTATUS DEN FÜR NBLINKSEXPIRATIONSTATUS DEN FÜR 12 Sekunden dauert das Ausführen, was nicht schnell genug für das ist, was ich zu erreichen versuche. Könnte es ein Problem () enthält und kann ich es auf irgendeine Weise schneller ausführen lassen? Ich arbeite mit EF6 und SQL Server. Bitte lassen Sie mich wissen, wenn es auch in diesem Fall besser SQLQuery verwenden kann oder nicht?

Code: Select all

HashSet oneEntitiesPerimeter = oneEquipment.Select(x => x.ID_ONE).ToHashSet();
HashSet twoEntitiesPerimeter = twoEquipment.Select(x => x.ID_TWO).ToHashSet();
< /code>
Die Methode, bei der es die Abfrage gibt: < /p>
public static Tuple NbLinksExpirationStatus(HashSet oneEntitiesPerimeter, HashSet twoEntitiesPerimeter)
{
using (DbEntities context = new DbEntities())
{
int aboutToExpireValue = Constants.LinkStatusAboutToExpire; #1
int expiredValue = Constants.LinkStatusExpired; #2

var oneIds = oneEntitiesPerimeter ?? new HashSet();
var twoIds = twoEntitiesPerimeter ?? new HashSet();

var joinedQuery = (from t in context.ONE_DISTRIBUTION_STATUS
join o in context.TWO_DISTRIBUTION_STATUS on t.ID_ent equals o.ID_ent into joined
from o in joined.DefaultIfEmpty()
where oneIds.Contains(t.ID_ONE) && (o == null || twoIds.Contains(o.ID_TWO))
select new { oneExpirationStatus = t.LINK_STATUS_EXPIRATION, twoExpirationStatus = o.LINK_STATUS_EXPIRATION }).ToList();

var aboutToExpireCount = joinedQuery.Where(j =>
j.oneExpirationStatus == aboutToExpireValue || j.twoExpirationStatus == aboutToExpireValue).Count();

var expiredCount = joinedQuery.Where(j =>
j.oneExpirationStatus == expiredValue || j.twoExpirationStatus == expiredValue).Count();

return new Tuple(aboutToExpireCount, expiredCount);
}
}
< /code>
Bearbeiten: Ich habe stattdessen versucht, SQL zu verwenden, aber die Geschwindigkeit der Ausführung verbessert sich immer nicht. Dies ist der Versuch: < /p>
string oneIds = "NULL";
if(oneEntitiesPerimeter != null && oneEntitiesPerimeter.Count > 0)
{
oneIds = string.Join(",", oneEntitiesPerimeter);
}
string twoIds = "NULL";
if (twoEntitiesPerimeter != null && twoEntitiesPerimeter.Count > 0)
{
twoIds = string.Join(",", twoEntitiesPerimeter);
}

string sqlQuery = "SELECT " +
"COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = " + aboutToExpireValue + " OR COALESCE(o.LINK_STATUS_EXPIRATION, '')= " + aboutToExpireValue + " THEN 1 ELSE 0 END), 0) AS AboutToExpire," +
"COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = " + expiredValue + " OR COALESCE(o.LINK_STATUS_EXPIRATION, '') = " + expiredValue + " THEN 1 ELSE 0 END), 0) AS Expired "+
"FROM ONE_DISTRIBUTION_STATUS t LEFT JOIN TWO_DISTRIBUTION_STATUS o ON t.ID_ent = o.ID_ent WHERE t.ID_ONE in (" + oneIds + ") AND (o.ID_TWO in (" + twoIds + ") OR o.ID_TWO IS NULL)";

var counter = context.Database.SqlQuery(sqlQuery).FirstOrDefault();
result = new Tuple(counter.AboutToExpire, counter.Expired);
< /code>
Die zum Speichern von Ergebnissen hinzugefügte Klasse: < /p>
public class LinkExpirationStatusWidget
{
/// 
/// Gets or sets count of links about to expire
/// 
/// About To Expire count
public int AboutToExpire { get; set; }

/// 
/// Gets or sets count of links expired
/// 
/// Expired count
public int Expired { get; set; }
}