LINQ to SQL 왼쪽 외부 조인

LINQ to SQL 왼쪽 외부 조인

---

이 쿼리는 LEFT OUTER조인 과 동일 합니까?

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

---

왼쪽 바깥 조인의 각 "왼쪽"행은 0-n "오른쪽"행 (두 번째 테이블)과 일치하므로 0-1 만 일치합니다. 왼쪽 외부 조인을 수행하려면, 당신은 필요 SelectMany하고 DefaultIfEmpty, 예를 들면 :

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.OrderID == null ? -1 : x.OrderID};   

( 또는 확장 방법을 통해 )

---

into 문은 필요하지 않습니다.

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

그리고 예, 위의 쿼리는 실제로 LEFT OUTER 조인을 만듭니다.

여러 개의 왼쪽 조인을 처리하는 비슷한 질문에 연결 : Linq to Sql : 여러 개의 왼쪽 외부 조인

---

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

http://msdn.microsoft.com/en-us/vbasic/bb737929.aspx 확인

---

1 솔루션을 찾았습니다. 이런 종류의 SQL (왼쪽 조인)을 Linq 엔티티로 변환하려면 ...

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

---

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.

---

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.

참고URL : https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join