UNIÓN EXTERNA IZQUIERDA en LINQ
¿Cómo realizar una unión externa izquierda en C# LINQ a objetos sin utilizar join-on-equals-intocláusulas? ¿ Hay alguna manera de hacer eso con wherela cláusula? Problema correcto: la unión interna es fácil y tengo una solución como esta
List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
select new JoinPair { LeftId = l.Id, RightId = r.Id})
pero para la unión externa izquierda necesito una solución. El mío es algo así pero no funciona.
List< JoinPair> leftFinal = (from l in lefts from r in rights
select new JoinPair {
LeftId = l.Id,
RightId = ((l.Key==r.Key) ? r.Id : 0
})
donde JoinPairhay una clase:
public class JoinPair { long leftId; long rightId; }
Como se indica en "Realizar uniones exteriores izquierdas" :
var q =
from c in categories
join pt in products on c.Category equals pt.Category into ps_jointable
from p in ps_jointable.DefaultIfEmpty()
select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };
Si se utiliza un proveedor LINQ basado en una base de datos, se puede escribir una unión externa izquierda significativamente más legible como tal:
from c in categories
from p in products.Where(c == p.Category).DefaultIfEmpty()
Si omites DefaultIfEmpty()tendrás una unión interna.
Tome la respuesta aceptada:
from c in categories
join p in products on c equals p.Category into ps
from p in ps.DefaultIfEmpty()
Esta sintaxis es muy confusa y no está claro cómo funciona cuando desea unirse por la izquierda a MÚLTIPLES tablas.
Nota
Cabe señalar que from alias in Repo.SomeTable.Where(condition).DefaultIfEmpty()es lo mismo que una aplicación externa/unión izquierda lateral, que cualquier optimizador de base de datos (decente) es perfectamente capaz de traducir en una unión izquierda, siempre y cuando no introduzca por fila -valores (también conocido como una aplicación exterior real). No hagas esto en Linq-2-Objects, porque allí no habrá ningún optimizador de base de datos...
Ejemplo detallado
var query2 = (
from users in Repo.T_User
from mappings in Repo.T_User_Group
.Where(mapping => mapping.USRGRP_USR == users.USR_ID)
.DefaultIfEmpty() // <== makes join left join
from groups in Repo.T_Group
.Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)
.DefaultIfEmpty() // <== makes join left join
// where users.USR_Name.Contains(keyword)
// || mappings.USRGRP_USR.Equals(666)
// || mappings.USRGRP_USR == 666
// || groups.Name.Contains(keyword)
select new
{
UserId = users.USR_ID
,UserName = users.USR_User
,UserGroupId = groups.ID
,GroupName = groups.Name
}
);
var xy = (query2).ToList();
Cuando se usa con LINQ 2 SQL, se traducirá muy bien a la siguiente consulta SQL muy legible:
SELECT
users.USR_ID AS UserId
,users.USR_User AS UserName
,groups.ID AS UserGroupId
,groups.Name AS GroupName
FROM T_User AS users
LEFT JOIN T_User_Group AS mappings
ON mappings.USRGRP_USR = users.USR_ID
LEFT JOIN T_Group AS groups
ON groups.GRP_ID == mappings.USRGRP_GRP
Insisto nuevamente, si está haciendo eso en Linq-2-Objects (en lugar de Linq-2-SQL), debe hacerlo a la antigua usanza (porque LINQ to SQL traduce esto correctamente para unir operaciones, pero sobre objetos esto método fuerza una exploración completa).
Esta es la forma antigua de hacerlo, con uniones izquierdas reales:
var query2 = (
from users in Repo.T_Benutzer
join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
from mappings in tmpMapp.DefaultIfEmpty()
from groups in tmpGroups.DefaultIfEmpty()
select new
{
UserId = users.BE_ID
,UserName = users.BE_User
,UserGroupId = mappings.BEBG_BG
,GroupName = groups.Name
}
);
Ahora, como ejemplo con una consulta más compleja, si necesita más explicaciones sobre cómo funciona, considere esta declaración SQL:
DECLARE @BE_ID integer;
DECLARE @stichtag datetime;
DECLARE @in_standort uniqueidentifier;
DECLARE @in_gebaeude uniqueidentifier;
SET @BE_ID = 123;
SET @stichtag = CURRENT_TIMESTAMP;
SET @in_standort = '00000000-0000-0000-0000-000000000000';
SET @in_gebaeude = '00000000-0000-0000-0000-000000000000';
DECLARE @unixTimestamp bigint;
DECLARE @bl national character varying(MAX);
SET @unixTimestamp = DATEDIFF(SECOND, '1970-01-01T00:00:00.000', CONVERT(DATETIME, @stichtag, 1));
SET @bl = (
SELECT TOP 1 FC_Value
FROM T_FMS_Configuration
WHERE FC_Key = 'basicLink'
);
-- SELECT @unixTimestamp AS unix_ts, @bl AS bl;
SELECT
so.SO_Nr AS RPT_SO_Nr
,so.SO_Bezeichnung AS RPT_SO_Bezeichnung
,gb.GB_Bezeichnung
,gb.GB_GM_Lat
,gb.GB_GM_Lng
,objTyp.OBJT_Code
,@bl + '/Modules/App150/index.html'
+ '?Code=' + COALESCE(objTyp.OBJT_Code, 'BAD')
+ '&UID=' + COALESCE(CAST(gb.GB_UID AS national character varying(MAX)), '')
+ '&Timestamp=' + CONVERT(national character varying(MAX), @unixTimestamp, 126)
AS RPT_QR
FROM T_AP_Gebaeude AS gb
LEFT JOIN T_AP_Standort AS so ON gb.GB_SO_UID = so.SO_UID
LEFT JOIN T_OV_Ref_ObjektTyp AS objTyp ON 'GB' = objTyp.OBJT_Code
LEFT JOIN T_Benutzer AS benutzer ON benutzer.BE_ID = @BE_ID AND benutzer.BE_Status = 1
WHERE gb.GB_Status = 1
AND @stichtag >= gb.GB_DatumVon
AND @stichtag <= gb.GB_DatumBis
AND so.SO_Status = 1
AND @stichtag >= so.SO_DatumVon
AND @stichtag <= so.SO_DatumBis
AND (@in_standort = '00000000-0000-0000-0000-000000000000' OR so.SO_UID = @in_standort)
AND (@in_gebaeude = '00000000-0000-0000-0000-000000000000' OR gb.GB_UID = @in_gebaeude)
AND
(
benutzer.BE_ID IS NULL
OR benutzer.BE_ID < 0
OR benutzer.BE_usePRT = 0
OR EXISTS
(
SELECT 1
FROM T_COR_Objekte AS obj
INNER JOIN T_COR_ZO_ObjektRechte_Lesen AS objR
ON objR.ZO_OBJR_OBJ_UID = obj.OBJ_UID
AND objR.ZO_OBJR_OBJ_OBJT_Code = obj.OBJ_OBJT_Code
WHERE obj.OBJ_UID = gb.GB_UID
)
);
lo que produce el siguiente LINQ:
(este es el contexto de la base de datos, el volcado es un método de LINQpad)
int BE_ID = 123;
System.DateTime stichtag = System.DateTime.Now;
System.Guid in_standort = System.Guid.Empty;
System.Guid in_gebaeude = System.Guid.Empty;
long unixTimestamp = (long)(stichtag.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalSeconds;
string bl = (
from c in this.T_FMS_Configuration
where c.FC_Key == "basicLink"
select c.FC_Value
).FirstOrDefault();
(
from gb in this.T_AP_Gebaeude
join so in this.T_AP_Standort on gb.GB_SO_UID equals so.SO_UID into gb_so
from so in gb_so.DefaultIfEmpty()
join objTyp in this.T_OV_Ref_ObjektTyp on "GB" equals objTyp.OBJT_Code into gb_objTyp
from objTyp in gb_objTyp.DefaultIfEmpty()
join benutzer in this.T_Benutzer.Where(b => b.BE_ID == BE_ID && b.BE_Status == 1) on 1 equals 1 into gb_benutzer
from benutzer in gb_benutzer.DefaultIfEmpty()
where gb.GB_Status == 1
&& stichtag >= gb.GB_DatumVon
&& stichtag <= gb.GB_DatumBis
&& so.SO_Status == 1
&& stichtag >= so.SO_DatumVon
&& stichtag <= so.SO_DatumBis
&& (in_standort == System.Guid.Empty|| so.SO_UID == in_standort)
&& (in_gebaeude == System.Guid.Empty || gb.GB_UID == in_gebaeude)
&&
(
benutzer == null
|| benutzer.BE_ID < 0
|| benutzer.BE_usePRT == false
|| this.T_COR_Objekte.Any(
obj => obj.OBJ_UID == gb.GB_UID
&& this.T_COR_ZO_ObjektRechte_Lesen.Any(objR => objR.ZO_OBJR_OBJ_UID == obj.OBJ_UID && objR.ZO_OBJR_OBJ_OBJT_Code == obj.OBJ_OBJT_Code)
)
)
select new {
RPT_SO_Nr = so.SO_Nr
,RPT_SO_Bezeichnung = so.SO_Bezeichnung
// ,RPT_GB_UID = gb.GB_UID
// ,gb.GB_Nr
,gb.GB_Bezeichnung
// ,adr = gb.GB_Strasse + " " + gb.GB_StrasseNr + ", CH-" + gb.GB_PLZ + " " + gb.GB_Ort
,gb.GB_GM_Lat
,gb.GB_GM_Lng
// ,objTyp.OBJT_UID
,objTyp.OBJT_Code
,RPT_QR = bl + "/Modules/App150/index.html"
+ "?Code=" + (objTyp.OBJT_Code ?? "BAD")
+ "&UID=" + (System.Convert.ToString(gb.GB_UID) ?? "" )
+ "&Timestamp=" + unixTimestamp.ToString(System.Globalization.CultureInfo.InvariantCulture)
}
).Dump();
Usando expresión lambda
db.Categories
.GroupJoin(db.Products,
Category => Category.CategoryId,
Product => Product.CategoryId,
(x, y) => new { Category = x, Products = y })
.SelectMany(
xy => xy.Products.DefaultIfEmpty(),
(x, y) => new { Category = x.Category, Product = y })
.Select(s => new
{
CategoryName = s.Category.Name,
ProductName = s.Product.Name
});
