LinqToSql "in" clause

Question

Is there an equivalent of SQL's IN clause, in LinqToSql?

For example, how would the following query be done in LinqToSql:

select * from users
left join groups on groups.pkid 
in (select group_id from user_group 
    where user_group.userid = users.userid)

Answer 1

The SQL 'in' equivalent in Linq and Linq-to-SQL is [arrayOrListOrOtherEnumerable].Contains(entity.someField).

The nearest [functionality wise] equivalent of your sample query would be:

from usr in dc.Users
join ug in dc.UserGroups on usr.UserID equals ug.UserID
join gr in dc.Group on ug.GroupID equals gr.PkID
select new { usr, gr }

I left out the left join from your query since the way it is written effectively forces the left join into an inner join. However, if you in some other situation want to do a left join, that is done by joining to a subquery appended by a .DefaultIfEmtpy.

An excellent resource on common constructs like this is Damien Guard's 'linq cheat sheet' - a printable one-pager PDF that can be downloaded from his blog at http://damieng.com/blog/2009/08/12/linq-to-sql-cheat-sheet