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  gt   100000 AND     o OrderNumber  lt   200000

User · Answer

I am using this linq query for my application  if this match your requirement you can refer this  here i have joined Left outer join  with 3 tables       Dim result    From csL In contractEntity CSLogin Where Function cs  cs Login   login AndAlso cs Password   password  DefaultIfEmpty                    From usrT In contractEntity UserType Where Function uTyp  uTyp UserTypeID   csL UserTyp  DefaultIfEmpty    lt    makes join left join                    From kunD In contractEntity EmployeeMaster Where Function kunDat  kunDat CSLoginID   csL CSLoginID  DefaultIfEmpty                    Select New With                      CSLoginID   csL CSLoginID                     UserType   csL UserTyp   ToList

User · Answer

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   gt  v Id    order VendorId           DefaultIfEmpty       from status      in dc Status          Where s   gt  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   gt  c Id    expense CategoryId                            DefaultIfEmpty           left join on expense type table if exists     from expenseType      in expenseDataContext ExpenseTypeDtos                           Where e   gt  e Id    expense ExpenseTypeId                            DefaultIfEmpty          left join on currency table if exists     from currency      in expenseDataContext CurrencyDtos                           Where c   gt  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

User · Answer

In VB NET using Function   Dim query   From order In dc Orders             From vendor In              dc Vendors Where Function v  v Id   order VendorId  DefaultIfEmpty               From status In              dc Status Where Function s  s Id   order StatusId  DefaultIfEmpty               Select Order   order  Vendor   vendor  Status   status

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   100000 And o OrderNumber  lt   200000               Select Vendor Name   x Name                       Order Number   o OrderNumber                       Status Name   y StatusName

User · Answer

In VB NET using Function   Dim query   From order In dc Orders             From vendor In              dc Vendors Where Function v  v Id   order VendorId  DefaultIfEmpty               From status In              dc Status Where Function s  s Id   order StatusId  DefaultIfEmpty               Select Order   order  Vendor   vendor  Status   status

User · Answer

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

User · Answer

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  gt   100000 And o OrderNumber  lt   200000               Select Vendor Name   x Name                       Order Number   o OrderNumber                       Status Name   y StatusName

User · Answer

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

User · Answer

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

User · Answer

I am using this linq query for my application  if this match your requirement you can refer this  here i have joined Left outer join  with 3 tables       Dim result    From csL In contractEntity CSLogin Where Function cs  cs Login   login AndAlso cs Password   password  DefaultIfEmpty                    From usrT In contractEntity UserType Where Function uTyp  uTyp UserTypeID   csL UserTyp  DefaultIfEmpty    lt    makes join left join                    From kunD In contractEntity EmployeeMaster Where Function kunDat  kunDat CSLoginID   csL CSLoginID  DefaultIfEmpty                    Select New With                      CSLoginID   csL CSLoginID                     UserType   csL UserTyp   ToList

User · Answer

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   gt  v Id    order VendorId           DefaultIfEmpty       from status      in dc Status          Where s   gt  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   gt  c Id    expense CategoryId                            DefaultIfEmpty           left join on expense type table if exists     from expenseType      in expenseDataContext ExpenseTypeDtos                           Where e   gt  e Id    expense ExpenseTypeId                            DefaultIfEmpty          left join on currency table if exists     from currency      in expenseDataContext CurrencyDtos                           Where c   gt  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

User · Answer

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

User · Answer

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

User · Answer

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  gt   100000 And o OrderNumber  lt   200000               Select Vendor Name   x Name                       Order Number   o OrderNumber                       Status Name   y StatusName

User · Answer

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  gt   100000 And o OrderNumber  lt   200000               Select Vendor Name   x Name                       Order Number   o OrderNumber                       Status Name   y StatusName

[vb.net] Linq to Sql: Multiple left outer joins

Examples related to vb.net

Examples related to linq

Examples related to linq-to-sql

Examples related to tsql

Examples related to left-join