Friday, January 20, 2012

Linq NOT IN clause

To emulate a SQL "NOT IN" clause like the following:

SELECT * FROM Clients WHERE ClientID NOT IN (select ClientID from AssignedClients)

We can do the following in Linq:

IEnumerable< Client > assignedClients = _repo.GetAssignedClients();
IEnumerable< Client > allClients = _repo.GetAllClients();
IEnumerable< Client > availableClients = from c in allClients
where !(assignedClients.Any(a => a.ClientID == c.ClientID))
select c;

Where Client has the following definition:

public class Client {
public int ClientID { get; set; }
public string ClientName { get; set; }
public bool IsActive { get; set; }
}


Another example:
var clientids = new int[] {1, 2, 3}; // IEnumerable of integers
var records = new Record[] { new Record{ClientId = 1}, new Record{ClientId = 9}, new Record{ClientId = 8}, new Record{ClientId = 2} };

var theRest = from r in records
              where !(clientids.Any(id => id == r.ClientId))
              select r;

And this is another way of obtaining the same
var theRest = records.Where(r => !clientids.Contains(r.ClientId));

They both will return the records whose ClientId is not in the list of clientids.

No comments: