Wednesday, March 10, 2010

How to write "in" and "Not in" query in Linq?

I was writing some simple queries in LINQ and got stuck with in-clase. I need to pick all the user roles from role table which are not assigned to any user i.e. not present in UserRole table. After a little googling I found that in LINQ there is no properly defined construct for it but people use different methods to acheive the functionality. The easiest one that worked for me is as follows.

I had to acheive this:

select * from roles where intRoleId not in (select ID from UserRoles)


var Roles = (from c in dc.Roles
where c.intClientId == clientId
&& (!dc.UserRoles.Any(ur => ur.intRoleId == c.ID))
select c).OrderBy(o => o.vchName);
returnRoles = Roles.ToList();

dc.UserRoles.Any() is the subquery and "!" sign is for "not in". If you want to write simple in-clause just remove this.


No comments:

Post a Comment