CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Alex Gurskiy
Alex Gurskiy

Reputation: 273

Optimize EF query using Any instead of contains

That was my query with Contains:

db.NavFilters.Where(finalExpression)
  .Count(x => x.Attribute1 == e.Attribute && x.Link == link && x.SubLink == subLink && db.NavItemsFilters
  .Where(n=> !(n.Promo == string.Empty || n.Promo == null))
  .Select(n=>n.ItemID)
  .Contains(x.ItemID) )

But, as far as I know, contains is a hard operation, and I need to optimize it. Is such query will give the same result?

db.NavFilters.Where(x=> db.NavItemsFilters.Any(n=>n.Promo != string.Empty && n.ItemID == x.ItemID))
  .Where(finalExpression)
  .Count(x => x.Attribute1 == e.Attribute && x.Link == link && x.SubLink == subLink)

I know, that the best solution is to add navigation properties. But I can't do that for many reasons.

Upvotes: 1

Views: 1104

Answers (2)

Alberto Monteiro
Alberto Monteiro

Reputation: 6219

You can optimize you query like this:

var navFilters = db.NavFilters.Where(finalExpression);

var thereIsAny = (from x in navFilters
                  join n in db.NavItemsFilters on x.ItemID equals n.ItemID 
                  where n.Promo != string.Empty && x.Attribute1 == e.Attribute && x.Link == link && x.SubLink == subLink
                  ).Any();

Upvotes: 2

user1023602
user1023602

Reputation:

IQueryable.Contains does exist, so your query should get converted to SQL.

Upvotes: 1

Related Questions