From Sql to Linq-toSql

Question

I'm using Sql Server 2012 and .NET 4

The Sql I'd like to convert to C# looks like this:

SELECT rd.Name, rd.Description, prdv.Value
FROM RoleDetail rd 
    JOIN Role r ON r.RoleID = rd.RoleID
    JOIN PersonRole pr ON pr.RoleID = r.RoleID
    LEFT OUTER JOIN PersonRoleDetailValue prdv ON prdv.RoleDetailID = rd.RoleDetailID
WHERE pr.PersonID = 42

After several attempts and much time wasted, I don't seem to be better off then when I started. Any help is appreciated.

I prefer method syntax, but query syntax solutions are more than welcome too.

Solutions (Thanks to David B's answer below)

Below are both working solutions. I hope others can benefit...

Method Syntax:

var methodSyntax = db.PersonRoles
    .Where(pr => pr.PersonID == 42)
    .SelectMany(pr => pr.Role.RoleDetails)
    .SelectMany(rd => rd.PersonRoleDetailValues.DefaultIfEmpty(), (rd, prdv) => new
        {
            Name = rd.Name,
            Description = rd.Description,
            Value = prdv.Value
        });

Query Syntax:

var querySyntax = from pr in db.PersonRoles
     where pr.PersonID == 42
     let r = pr.Role
     from rd in r.RoleDetails
     from prdv in rd.PersonRoleDetailValues.DefaultIfEmpty()
     select new
     {
         Name = rd.Name,
         Description = rd.Description,
         Value = prdv.Value
     };

Thank you

Answer 1

If your mapping is set up right, there should be navigational properties between your types. This lets you start a query about one type and include other types without specifying the joined columns over and over.

from pr in dataContext.PersonRoles
where pr.PersonId = 42
  //navigation property Many->One  Queryable.Select
let r = pr.Role  
  //navigation property One->Many  Queryable.SelectMany
from rd in r.RoleDetails
  //navigation property One->Many, match null if none
from prdv = rd.PersonRoleDetailValues.DefaultIfEmpty()
select new
{
  Name = rd.Name,
  Description = rd.Description,
  Value = prdv.Value
}

And for fun, method syntax as terse as I can get:

dataContext.PersonRoles
  .Where(pr => pr.PersonId = 42)
  .SelectMany(pr => pr.Role.RoleDetails)
  .SelectMany(rd => rd.PersonRoleDetailValues.DefaultIfEmpty(),
    (rd, prdv) => new
  {
    Name = rd.Name,
    Description = rd.Description,
    Value = prdv.Value
  });