Unión Externa Izquierda de LINQ a SQL

¿Es esta consulta equivalente a una combinación 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
}
Author: Svetlozar Angelov, 2009-03-31
Source

5 answers

No del todo - ya que cada fila "izquierda" en una unión-externa-izquierda coincidirá con 0-n filas "derecha" (en la segunda tabla), donde-como la suya solo coincide con 0-1. Para hacer una unión externa izquierda, necesitas SelectMany y DefaultIfEmpty, por ejemplo: 

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

(o a través de los métodos de extensión )

 148
Author: Marc Gravell,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:34:17

No necesitas las declaraciones 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

Y sí, la consulta anterior de hecho crea una unión EXTERNA IZQUIERDA.

Enlace a una pregunta similar que maneja múltiples uniones a la izquierda: Linq to Sql: Múltiples uniones externas a la izquierda

 184
Author: Amir,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:26:14 
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

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

 12
Author: Krishnaraj Barvathaya,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-11 11:58:08

Encontré 1 solución. si desea traducir este tipo de SQL (unión izquierda) en la entidad 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...
}
 4
Author: mokth,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-23 02:58:02

Me gustaría añadir una cosa más. En LINQ to SQL si su base de datos está correctamente construida y sus tablas están relacionadas a través de restricciones de clave externa, entonces no necesita hacer una combinación en absoluto.

Usando LINQPad creé la siguiente consulta LINQ:

//Querying from both the CustomerInfo table and OrderInfo table
from cust in CustomerInfo
where cust.CustomerID == 123456
select new {cust, cust.OrderInfo}

Que se tradujo a la consulta (ligeramente truncada) a continuación

 -- 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]

Observe el LEFT OUTER JOIN anterior.

 1
Author: Brian Kraemer,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-08-06 04:04:09