LINQ Fluent API version of left join query

Question

I currently have a LINQ query that is working just fine but I would like to know how to convert it to be in the LINQ fluent API format. I have tried searching Google for a decent tutorial on fluent API to try and learn this my self but there does not seem to be one.

This is the query I want to convert:

from s in db.Sections
join f in db.Files
    on s.LogoFileID equals f.ID into s_f
where s.RouteName == SectionRoute
from x in s_f.DefaultIfEmpty()
select new GameSectionVM
{
    SectionID = s.ID,
    GameTitle = s.Title,
    LogoFileName = x.FileName,
    Synopsis = s.Synopsi
}).Single();

Answer 1

ReSharper gives the following conversion:

(db.Sections.GroupJoin(db.Files, s => s.LogoFileID, f => f.ID, (s, s_f) => new { s, s_f })
          .Where(@t => s.RouteName == SectionRoute)
          .SelectMany(@t => s_f.DefaultIfEmpty(), (@t, x) => new GameSectionVM
                                                             {
                                                                 SectionID = s.ID,
                                                                 GameTitle = s.Title,
                                                                 LogoFileName = x.FileName,
                                                                 Synopsis = s.Synopsi
                                                             })).Single();

I cannot vouch for its accuracy, but it should at least help you get started. the GroupJoin appears to be what you need to do the outer join at least.

Answer 2

It's hard to tell exactly what would work in your circumstance because you haven't provided the details of all the involved types; but, something along the lines of this:

(db.Sections.GroupJoin(db.Files, s => s.LogoFileID, f => f.ID, (s, s_f) => new {s, s_f})
    .Where(t => t.s.RouteName == SectionRoute)
    .SelectMany(t => t.s_f.DefaultIfEmpty(), (t, x) => new GameSectionVM
    {
        SectionID = t.s.ID,
        GameTitle = t.s.Title,
        LogoFileName = x.FileName,
        Synopsis = t.s.Synopsis
    })).Single();

Assumes the Section class exists and has a correctly named property Synopsis (not Sysnopsi)