How do I put a left join between two tables in Linq-to-SQL using the keys?

Question

I wrote query in Linq to SQL. I need to put left join between db.secs and db.subs i.e. with db.subs being left table and the db.secs being right.

I wrote this but cannot figure out how to do that?

var qry = (from sr in db.secs
           join s in db.subs
           on sr.Id equals s.secId
           join ss in db.subsSt
           on s.Id equals ss.subId
           join u in db.usersNew
           on s.uid equals u.Id
           where ss.isNew 
           group s by new { s.uid, u.UName, sr.Id, sr.Name } into totalGrp
           select new
           {
               CreatorName = totalGrp.Key.UName,
               SecName = totalGrp.Key.Name,
               TotalRecs = totalGrp.Count()
           }).OrderBy(o => o.CreatorName)
           .ToList();

How do I make it re-arrange like first table? In angular and HTML I am looping through collection and presenting in table.

Answer 1

You have to use DefaultIfEmpty() on the second table to generate the outer join or use navigation properties on the store. Something along the lines of

var query = from sr in db.secs
            join s in db.subs into secSubs
            from srs in secSubs.DefaultIfEmpty()
            // rest of the query follows

Alternatively with navigation properties you might be able to do something like

var query = from sr in db.secs
            from s in sr.Subs
            select new {sr, s}

A third option is to possibly use a quasi ANSI-82 syntax rather than ANSI-92 join syntax and make the first item above more pallatable:

var query = from sr in db.secs
            from s in db.Subs.Where(s1 => s1.subId == sr.Id).DefaultIfEmpty()
            select new {sr, s}

I wrote up a more detailed post on this a while back at https://www.thinqlinq.com/Post.aspx/Title/Left-Outer-Joins-in-LINQ-with-Entity-Framework.