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

Issue

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.

Solution

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.

Answered By - Jim Wooley

Answer Checked By - Timothy Miller (PHPFixing Admin)