Wednesday, August 11, 2010


So, while trying to get a fairly straight forward (I thought) select ... from ... where ... not in (...) I found that there is not really an equivalent in LINQ, or so I thought...

Read the solution here:
var orphans = (from c in context.Instances
orderby c.Title
where !(from o in context.CategoryInstances
select o.InstanceID)
select c).Skip(PageIndex * PageSize).Take(PageSize);

Essentially, the query does the same thing in C# - that is, to get the list of IDs to check within and then check within it for the ID which is hoping to be not found. Because the whole thing is compiled into SQL before before executed, LINQ generates a NOT EXISTS statement in the WHERE clause, rather than the slower NOT IN.

1 comment: