Linq to Sql: Multiple left outer joins

Question

I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL. I understand how to use one left outer join. I'm using VB.NET. Below is my SQL syntax.

T-SQL

SELECT
    o.OrderNumber,
    v.VendorName,
    s.StatusName
FROM
    Orders o
LEFT OUTER JOIN Vendors v ON
    v.Id = o.VendorId
LEFT OUTER JOIN Status s ON
    s.Id = o.StatusId
WHERE
    o.OrderNumber >= 100000 AND
    o.OrderNumber <= 200000

Answer 1

I think you should be able to follow the method used in this post. It looks really ugly, but I would think you could do it twice and get the result you want.

I wonder if this is actually a case where you'd be better off using DataContext.ExecuteCommand(...) instead of converting to linq.

Answer 2

Don't have access to VisualStudio (I'm on my Mac), but using the information from http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx it looks like you may be able to do something like this:

var query = from o in dc.Orders
            join v in dc.Vendors on o.VendorId equals v.Id into ov
            from x in ov.DefaultIfEmpty()
            join s in dc.Status on o.StatusId equals s.Id into os
            from y in os.DefaultIfEmpty()
            select new { o.OrderNumber, x.VendorName, y.StatusName }

Answer 3

I figured out how to use multiple left outer joins in VB.NET using LINQ to SQL:

Dim db As New ContractDataContext()

Dim query = From o In db.Orders _
            Group Join v In db.Vendors On v.VendorNumber Equals o.VendorNumber Into ov = Group _
            From x In ov.DefaultIfEmpty() _
            Group Join s In db.Status On s.Id Equals o.StatusId Into os = Group _
            From y In os.DefaultIfEmpty() _
            Where o.OrderNumber >= 100000 And o.OrderNumber <= 200000 _
            Select Vendor_Name = x.Name, _
                   Order_Number = o.OrderNumber, _
                   Status_Name = y.StatusName

Answer 4

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