What is the difference between IQueryable .Where and IQueryable.Except?

Question

I tried to use Except method. I thought this method was more powerfull and generates a cleaner SQL than a WHERE clause but not. It generates the same sql.

So why / when use Except method ?

EDIT : here is a sample :

// Get customers except those which ID are in the LostCustomers table
TblCustomers.Except(TblCustomers.Where(tj => LostCustomers.Select(lj => lj.CustomerId).Contains(tj.CustomerID))).Select(j => new
{
    CustomerId = j.CustomerID,
    CustomerRef = j.CustomerRef,
    CustomerName = j.Name
})

// Get customers except those which ID are in the LostCustomers table
TblCustomers.Where(tj => !LostCustomers.Select(lj => lj.CustomerId).Contains(tj.CustomerID)).Select(j => new
{
    CustomerId = j.CustomerID,
    CustomerRef = j.CustomerRef,
    CustomerName = j.Name
})

Thx

The two methods are entirely different. Are you talking about `Where` with some specific condition? — Sergey Kalinichenko, Dec 08 '16 at 19:40
Can you show your examples of how you get identical SQL using each one. — juharr, Dec 08 '16 at 19:42

score 0 · Answer 1 · answered Dec 08 '16 at 19:43

0

It's rarely worthwhile to choose a LINQ pattern based on what SQL you think it will produce. Instead, use whichever method seems to give you the clearest code. For example, I would consider Except to be clearer in this case:

var taskIds = db.Tasks.Select(task => task.Id);
var doneTaskIds = db.TaskCompletions.Select(tc => tc.TaskId);

var unfinishedTaskIds = taskIds.Except(doneTaskIds);

as opposed to:

var unfinishedTaskIds = taskIds.Where(id => !doneTaskIds.Contains(id));

But there are many, many cases where Where() makes more sense. Chances are, in the example above, you could just say this, which would be even clearer:

var unfinishedTasks = db.Tasks.Where(task => !task.TaskCompletions.Any());

answered Dec 08 '16 at 19:43

StriplingWarrior

151,543
27
246
315

Yes I agree, but finally Where is more easy to use because we can pass a predicate in the signature. It's less constraining – Florian Dec 09 '16 at 16:23
Yes, `Where` is far more flexible. – StriplingWarrior Dec 09 '16 at 16:47
PS--in this case, "ultimately," "in the end," or "when all is said and done" would be a better word choice than "finally." (I'm assuming you're directly translating from *finalement*, n'est ce pas?) – StriplingWarrior Dec 09 '16 at 16:50

What is the difference between IQueryable .Where and IQueryable.Except?

1 Answers1