Linq to SQL Multiple Tables Left Outer Join

LINQ to SQL multiple tables left outer join

Here is how left outer joins are implemented with LINQ. You should use GroupJoin (join...into syntax):

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm
     on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
     on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()
// ... other joins here
where d.processid == 6 &&
      ((m.branchId == 1 && d.DestinationBranchID == 0) ||
       (d.DestinationBranchID == 1 && d.sendstatus == "R"))
// ... other conditions here
orderby d.priority descending, m.bookingid
select new {
   d.bookingid,
   d.labid,
   d.processid,
   p.prid,
   p.prno,
   m.bookingid // need for grouping
} into x
group x by x.bookingid into g
select g

This query joins three tables. You can join the rest of the tables the same way.

Complex joins in Linq with multiple tables and LEFT OUTER JOIN

You should apply Left Join this way:

join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
from readers in readersJ.DefaultIfEmpty()

The full code:

var query = (
    from tags in db.Tags
    join tagreads in db.tag_reads on tags.epc equals tagreads.epc
    join readerdata in db.ReaderData on tagreads.ReaderDataId equals readerdata.Id
    join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
    from readers in readersJ.DefaultIfEmpty()
    join locations in db.Locations 
        on new { ap = tagreads.antennaPort, rd = readerdata.Id }
        equals new { ap = locations.AntennaId, rd = locations.ReaderId }
    group tags by new { tags.TagFriendlyName, timestamp = tagreads.timeStamp, readerdata.mac_address } into grp

    select new CurrentStatus()
    {
        TagName = grp.Key.TagFriendlyName,
        LastSeen = grp.Key.timestamp,
        LocationName = grp.Key.mac_address
    }
)
.OrderByDescending(o => o.LastSeen)

LINQ Left Outer Join Multiple Tables with Group Count and Row Concatenation

Assuming you are using EF, and you have navigation properties set up, then your query would look like this:

var result = context.Parents
  .Select(p => new {
    p.ParentId,
    p.ParentName,
    p.ParentOccupation,
    NumberOfGrandChildren = p.Children
       .SelectMany(c => c.GrandChildren)
       .Count(),
    NamesOfGrandChildren = string.Join(", ", p.Children
      .SelectMany(c => c.GrandChildren)
      .Select(g => g.GrandChildName))
  }).ToList();

LINQ for an SQL with multiple LEFT OUTER JOINS on different tables

You can simply convert your query to linq query like this:

var results = (from a in A_TABLE
               join b in B_TABLE
               on a.A_TABLE_Id equals b.A_TABLE_Id into ab
               from b in ab.DefaultIfEmpty()
               join c in C_TABLE_List on b.B_TABLE_Id equals c.B_TABLE_Id

               select new
                   {
                     ClassAProperty1 = a.Property1,
                     ClassBProperty1 = b.Property1,
                     ClassCProperty1 = c.Property1
                   }).ToList();

you can continue on joining tables whatever times you need.

Don't forget to change Property# to required property names.

for more info take a look at LINQ Left Outer Join, and this stack thread.

Update:
this is the Lambda expressions version of the query:

var result = A_TABLE
             .GroupJoin(B_TABLE,
                 a => a.A_TABLE_Id,
                 b => b.A_TABLE_Id,
                 (a, b) =>
                 new {
                       tableAProperty1 = a.Property1,
                       tableAProperty2 = a.Property2, /* You cannot access this property in next join if you don't add it here */
                       B_TABLE = b.FirstOrDefault() /* This is the way to access B_TABLE in next join */
                 }).GroupJoin(C_TABLE,
                       ab => ab.B_TABLE.B_TABLE_Id,
                       c => c.B_TABLE_Id,
                       (ab, c) =>
                       new {
                             ab.tableAProperty1,
                             B_TABLEProperty2 = ab.B_TABLE.Property2,
                             C_TABLE = c.FirstOrDefault()
                            }).ToList();

SQL to LINQ - multiple tables left outer join with where clause referring right table

Your SQL query seems very questionable to me (since ux is a LEFT JOIN and (presumably) not referenced in the rest of the query, it adds nothing), and I had to assume the ORDER BY was on the g columns since it wasn't specified (and I am surprised that isn't rejected by SQL as ambiguous), but here is my try at the translation:

var ans = from g in MD
          join ux in MD on new { TXT_Page = sPage, g.TXT_Property, TXT_Product = sProdAll, GID_Section = (string)null } equals new { ux.TXT_Page, ux.TXT_Property, ux.TXT_Product, ux.GID_Section } into uxj
          from ux in uxj.DefaultIfEmpty()
          join up in MD on new { TXT_Page = sPage, g.TXT_Property, GID_Section = (string)null } equals new { up.TXT_Page, up.TXT_Property, up.GID_Section } into upj
          from up in upj.DefaultIfEmpty()
          where up == null || (up.TXT_Product ?? "") == sProd || up.TXT_Product == sProdAlt
          where g.GID_Section == null && g.TXT_Page == sPage &&
                ((g.TXT_Product ?? "") == sProd || g.TXT_Product == sProdAlt || g.TXT_Product == sProdAll)
          orderby g.TXT_Property, g.TXT_Product, g.TXT_Language
          select new { g.TXT_Property, up.TXT_Page };

Note: I put in the up == null because because otherwise the where might reject where the LEFT JOIN in SQL wouldn't.

I assumed the type of GID_Section was string, but you can cast the nulls to the right type.

Here is my SQL conversion recipe, though your SQL was a bit trickier since it combined LEFT JOIN and non-equijoin.

For translating SQL to LINQ query comprehension:

Translate FROM subselects as separately declared variables.
Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
Use table aliases as range variables. Use column aliases as anonymous type field names.
Use anonymous types (new { ... }) for multiple columns.
JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where
JOIN conditions that are multiple ANDed equality tests between the two tables should be translated into anonymous objects
LEFT JOIN is simulated by using into joinvariable and doing another from from the joinvariable followed by .DefaultIfEmpty().
Replace COALESCE with the conditional operator (?:)and a null test.
Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
Translate x BETWEEN low AND high to low <= x && x <= high.
Translate CASE to the ternary conditional operator ?:.
SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
Proper FULL OUTER JOIN must be handled with an extension method.

How to left join multiple tables with LINQ

From Linq - left join on multiple (OR) conditions :

IQueryable<Job> jobs = (from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jtResult.UserId equals u.Id into jtU
                from jtUResult in jtU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j).Concat(
                from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j
                ).Distinct()

SQL to Linq multiple tables left outer join

you can just use from var in collection join in syntax, something like this:

using(var cxt = new YourDataBaseContext()){
    var firstJoin = from t in cxt.BookReceiptMast
                    join y in cxt.BookReceiptDtl
                         on t.BookReceiptMastId equals y.BookReceiptMastId
                     into yTemp
                    from y in yTemp.DefaultIfEmpty()
                    select new
                    {
                        Id = y != null ? y.BookMastId : 0,
                        vrsn = t.VrsnMastId
                    };

    var allTables = from p in cxt.BookMast
                    join s in firstJoin
                         on p.BookMastId equals s.Id
                         into sTemp
                    from s in sTemp
                    where s.vrsn == 2
                    select new
                    {
                        mastId = p.BookMastId
                    };
}

I hope it helps you.

Linq to Sql: Multiple left outer joins

This may be cleaner (you dont need all the into statements):

var query = 
    from order in dc.Orders
    from vendor 
    in dc.Vendors
        .Where(v => v.Id == order.VendorId)
        .DefaultIfEmpty()
    from status 
    in dc.Status
        .Where(s => s.Id == order.StatusId)
        .DefaultIfEmpty()
    select new { Order = order, Vendor = vendor, Status = status } 
    //Vendor and Status properties will be null if the left join is null

Here is another left join example

var results = 
    from expense in expenseDataContext.ExpenseDtos
    where expense.Id == expenseId //some expense id that was passed in
    from category 
    // left join on categories table if exists
    in expenseDataContext.CategoryDtos
                         .Where(c => c.Id == expense.CategoryId)
                         .DefaultIfEmpty() 
    // left join on expense type table if exists
    from expenseType 
    in expenseDataContext.ExpenseTypeDtos
                         .Where(e => e.Id == expense.ExpenseTypeId)
                         .DefaultIfEmpty()
    // left join on currency table if exists
    from currency 
    in expenseDataContext.CurrencyDtos
                         .Where(c => c.CurrencyID == expense.FKCurrencyID)
                         .DefaultIfEmpty() 
    select new 
    { 
        Expense = expense,
        // category will be null if join doesn't exist
        Category = category,
        // expensetype will be null if join doesn't exist
        ExpenseType = expenseType,
        // currency will be null if join doesn't exist
        Currency = currency  
    }

Linq to Sql multiple outer joins

This should work:

var ans = from lbi in lbContext.lb_item
          where lbi.itemID == id
          join lbp in lbContext.lb_pattern on lbi.patternID equals lbp.patternID into lbpj
          from lbp in lbpj.DefaultIfEmpty()
          join lba in lbContext.lb_animal on lbi.itemID equals lba.itemID into lbaj
          from lba in lbaj.DefaultIfEmpty()
          join lbc in lbContext.lb_category on lbi.categoryID equals lbc.categoryID
          select new {
          };

Linq to SQL Multiple Tables Left Outer Join