LINQ to SQL Left Outer Join

Question

Is this query equivalent to a LEFT OUTER join     assuming that I have a parameter named  invoiceId  of type int from c in SupportCases let invoice   c Invoices FirstOrDefault i  gt  i Id    invoiceId  where  invoiceId    0    invoice    null      select new          Id   c Id         InvoiceId   invoice    null   0   invoice Id

User · Accepted Answer

Not quite - since each  quot left quot  row in a left-outer-join will match 0-n  quot right quot  rows  in the second table   where-as yours matches only 0-1  To do a left outer join  you need SelectMany and DefaultIfEmpty  for example  var query   from c in db Customers             join o in db Orders                on c CustomerID equals o CustomerID into sr             from x in sr DefaultIfEmpty               select new                  CustomerID   c CustomerID  ContactName   c ContactName                 OrderID   x    null   -1   x OrderID         or via the extension methods

User · Answer

I d like to add one more thing  In LINQ to SQL if your DB is properly built and your tables are related through foreign key constraints  then you do not need to do a join at all   Using LINQPad I created the following LINQ query     Querying from both the CustomerInfo table and OrderInfo table from cust in CustomerInfo where cust CustomerID    123456 select new  cust  cust OrderInfo    Which was translated to the  slightly truncated  query below   -- Region Parameters  DECLARE  p0 Int   123456 -- EndRegion SELECT  t0   CustomerID    t0   AlternateCustomerID     t1   OrderID    t1   OnlineOrderID         SELECT COUNT        FROM  OrderInfo  AS  t2      WHERE  t2   CustomerID     t0   CustomerID        AS  value  FROM  CustomerInfo  AS  t0  LEFT OUTER JOIN  OrderInfo  AS  t1  ON  t1   CustomerID     t0   CustomerID  WHERE  t0   CustomerID     p0 ORDER BY  t0   CustomerID    t1   OrderID    Notice the LEFT OUTER JOIN above

User · Answer

I found 1 solution  if want to translate this kind of SQL  left join  into Linq Entity     SQL   SELECT   FROM  JOBBOOKING  AS  t0  LEFT OUTER JOIN  REFTABLE  AS  t1  ON   t0   trxtype     t1   code                                     AND   t1    reftype     TRX     LINQ   from job in JOBBOOKINGs join r in  from r1 in REFTABLEs where r1 Reftype   TRX  select r1             on job Trxtype equals r Code into join1 from j in join1 DefaultIfEmpty   select new        cols

User · Answer

You don t need the into statements   var query        from customer in dc Customers     from order in dc Orders           Where o   gt  customer CustomerId    o CustomerId            DefaultIfEmpty       select new   Customer   customer  Order   order          Order will be null if the left join is null   And yes  the query above does indeed create a LEFT OUTER join   Link to a similar question that handles multiple left joins  Linq to Sql  Multiple left outer joins

User · Answer

Public Sub LinqToSqlJoin07   Dim q   From e In db Employees           Group Join o In db Orders On e Equals o Employee Into ords   Group           From o In ords DefaultIfEmpty           Select New With  e FirstName  e LastName   Order   o   ObjectDumper Write q  End Sub   Check http   msdn microsoft com en-us vbasic bb737929 aspx

User · Answer

Take care of performance   I experienced that at least with EF Core the different answers given here might result in different performance  I m aware that the OP asked about Linq to SQL  but it seems to me that the same questions occur also with EF Core   In a specific case I had to handle  the  syntactically nicer  suggestion by Marc Gravell resulted in left joins inside a cross apply -- similarly to what Mike U described -- which had the result that the estimated costs for this specific query were two times as high compared to a query with no cross joins  The server execution times differed by a factor of 3   1   The solution by Marc Gravell resulted in a query without cross joins   Context  I essentially needed to perform two left joins on two tables each of which again required a join to another table  Furthermore  there I had to specify other where-conditions on the tables on which I needed to apply the left join  In addition  I had two inner joins on the main table   Estimated operator costs    with cross apply  0 2534 without cross apply  0 0991    Server execution times in ms  queries executed 10 times  measured using SET STATISTICS TIME ON     with cross apply  5  6  6  6  6  6  6  6  6  6 without cross apply  2  2  2  2  2  2  2  2  2  2    The very first run was slower for both queries  seems that something is cached    Table sizes    main table  87 rows  first table for left join  179 rows  second table for left join  7 rows    EF Core version  2 2 1   SQL Server version  MS SQL Server 2017 - 14     on Windows 10    All relevant tables had indexes on the primary keys only   My conclusion  it s always recommended to look at the generated SQL since it can really differ      1  Interestingly enough  when setting the  Client statistics  in MS SQL Server Management Studio on  I could see an opposite trend  namely that last run of the solution without cross apply took more than 1s  I suppose that something was going wrong here - maybe with my setup

[.net] LINQ to SQL Left Outer Join

Examples related to .net

Examples related to database

Examples related to linq

Examples related to linq-to-sql