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
}

5 个解决方案

#1

142

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:

不是完全的——因为左外连接中的每个“左”行将匹配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};

(or via the extension methods)

(或通过扩展方法)

#2

178

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

链接到处理多个左连接的类似问题:Linq to Sql:多个左外连接。

#3

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

检查http://msdn.microsoft.com/en-us/vbasic/bb737929.aspx

#4

I found 1 solution. if want to translate this kind of SQL (left join) into Linq Entity...

我发现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...
}

#5

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.

我想再加一件事。在LINQ to SQL中，如果您的DB被正确地构建，并且您的表与外键约束相关，那么您就不需要做一个连接。

Using LINQPad I created the following LINQ query:

使用LINQPad我创建了以下LINQ查询:

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

注意上面的左外连接。

#1

142

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