CODEWITHSUNDEEP
.netdatabaselinqlinq-to-sql
Ali Kazmi
Ali Kazmi

Reputation: 3660

LINQ to SQL Left Outer Join

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=> i.Id == invoiceId)
where (invoiceId == 0 || invoice != null)    
select new 
{
      Id = c.Id
      , InvoiceId = invoice == null ? 0 : invoice.Id
}

Upvotes: 152

Views: 173529

Answers (6)

Marc Gravell
Marc Gravell

Reputation: 1062695

Not quite - since each "left" row in a left-outer-join will match 0-n "right" 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)

Upvotes: 186

Andreas Schütz
Andreas Schütz

Reputation: 11

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.

Upvotes: 1

Amir
Amir

Reputation: 9192

You don't need the into statements:

var query = 
    from customer in dc.Customers
    from order in dc.Orders
         .Where(o => 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

Upvotes: 244

Brian Kraemer
Brian Kraemer

Reputation: 453

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.

Upvotes: 3

mokth
mokth

Reputation: 113

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...
}

Upvotes: 5

Krishnaraj Barvathaya
Krishnaraj Barvathaya

Reputation: 595

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

Upvotes: 14

Related Questions