CODEWITHSUNDEEP
sqllinq
Peter Wone
Peter Wone

Reputation: 18739

LINQ exclusion

Is there a direct LINQ syntax for finding the members of set A that are absent from set B? In SQL I would write this

SELECT A.* FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID IS NULL

Upvotes: 4

Views: 3689

Answers (3)

Quassnoi
Quassnoi

Reputation: 425261

See the MSDN documentation on the Except operator.

Upvotes: 11

Jeff Yates
Jeff Yates

Reputation: 62367

I believe your LINQ would be something like the following.

var items = A.Except(
  from itemA in A
  from itemB in B
  where itemA.ID == itemB.ID
  select itemA);

Update

As indicated by Maslow in the comments, this may well not be the most performant query. As with any code, it is important to carry out some level of profiling to remove bottlenecks and inefficient algorithms. In this case, chaowman's answer provides a better performing result.

The reasons can be seen with a little examination of the queries. In the example I provided, there are at least two loops over the A collection - 1 to combine the A and B list, and the other to perform the Except operation - whereas in chaowman's answer (reproduced below), the A collection is only iterated once.

// chaowman's solution only iterates A once and partially iterates B
var results = from itemA in A
              where !B.Any(itemB => itemB.Id == itemA.Id)
              select itemA;

Also, in my answer, the B collection is iterated in its entirety for every item in A, whereas in chaowman's answer, it is only iterated upto the point at which a match is found.

As you can see, even before looking at the SQL generated, you can spot potential performance issues just from the query itself. Thanks again to Maslow for highlighting this.

Upvotes: 3

Chaowlert Chaisrichalermpol
Chaowlert Chaisrichalermpol

Reputation: 5402

var results = from itemA in A
              where !B.Any(itemB => itemB.Id == itemA.Id)
              select itemA;

Upvotes: 3

Related Questions