Linq To SQL Contains

Question

How can I go about converting this SQL statement to LINQ:

SELECT     [Content].[Content], [Content].ListOrder, [Content].ContentTypeId,        
           [Content].ContentId
FROM       [Content] INNER JOIN
           GroupContentPermission ON [Content].ContentId = GroupContentPermission.ContentId 
WHERE      GroupContentPermission.GroupId IN 
           (SELECT GroupId FROM GroupUser WHERE GroupUser.UserId = 169)

Answer 1

Translation to LINQ is generally pretty straightforward except for one special trick in the case of your query. You can translate your select, where, and from statements in a natural way as shown below. In the case of an IN statement though, you have to get the results from the inner subquery first, and then check if the inner subquery .Contains the value you want to check.

var groups =
   (from gu in GroupUser
    where gu.UserId == 169
    select gu.GroupId).ToList();

var result = 
    from p in GroupContentPermission
    join c in Content on p.ContentId equals c.ContentId
    where groups.Contains(p.GroupId)
    select new { c.Content, c.ListOrder, c.ContentTypeID, c.ContentId };

// result should contain the same results as the SQL query

Here are some other resources you may find helpful as well (you can find many more resources and tutorials on LINQ if you do a quick google search. There are literally thousands):

• Linqer, a SQL to LINQ converter.
• LinqPAD, a simple .NET/LINQ tester for rapid experimentation
• ScottGu's definitive guide to Using LINQ To SQL
• Related SO question: What are some good LINQ resources?, which references a tutorial called 101 LINQ Samples.

Answer 2

Assuming you already link the tables with foreign keys in your model (DBML/EntityFrameworks):

Contents.Where(x => x.GroupContentPermission.GroupUser.UserId == 169).Select(x => new {
    x.Content,
    x.ListOrder,
    x.ContentTypeId,
    x.ContentId })

or preferrably just grab the full Content object, and use any column you want:

var contents = Contents.Where(x => x.GroupContentPermission.GroupUser.UserId == 169).ToList();

foreach (var content in contents)
    Console.Write(content.Content);