Joins and subqueries in LINQ

Question

I am trying to do a join with a sub query and can't seem to get it. Here is what is looks like working in sql. How do I get to to work in linq?

SELECT po.*, p.PermissionID
FROM PermissibleObjects po
 INNER JOIN PermissibleObjects_Permissions po_p ON (po.PermissibleObjectID = po_p.PermissibleObjectID)
 INNER JOIN Permissions p ON (po_p.PermissionID = p.PermissionID)
 LEFT OUTER JOIN
  (
  SELECT u_po.PermissionID, u_po.PermissibleObjectID
  FROM Users_PermissibleObjects u_po
  WHERE u_po.UserID = '2F160457-7355-4B59-861F-9871A45FD166'
  ) used ON (p.PermissionID = used.PermissionID AND po.PermissibleObjectID = used.PermissibleObjectID)
WHERE used.PermissionID is null

Answer 1

Without seeing your database and data model, it's pretty impossible to offer any real help. But, probably the best way to go is:

• download linqpad - http://www.linqpad.net/
• create a connection to your database
• start with the innermost piece - the subquery with the "where" clause
• get each small query working, then join them up. Linqpad will show you the generated SQL, as well as the results, so build your small queries up until they are right

So, basically, split your problem up into smaller pieces. Linqpad is fantastic as it lets you test these things out, and check your results as you go

hope this helps, good luck

Toby

Answer 2

The LINQ translation for your query is suprisingly simple:

from pop in PermissibleObjectPermissions
where !pop.UserPermissibleObjects.Any (
  upo => upo.UserID == new Guid ("2F160457-7355-4B59-861F-9871A45FD166"))
select new { pop.PermissibleObject, pop.PermissionID }

In words: "From all object permissions, retrieve those with at least one user-permission whose UserID is 2F160457-7355-4B59-861F-9871A45FD16".

You'll notice that this query uses association properties for navigating relationships - this avoids the need for "joining" and simplfies the query. As a result, the LINQ query is much closer to its description in English than the original SQL query.

The trick, when writing LINQ queries, is to get out of the habit of "transliterating" SQL into LINQ.