如果使用基于数据库的LINQ提供程序,可以将一个更易读的左外连接写成这样:
from c in categories
from p in products.Where(c == p.Category).DefaultIfEmpty()
如果省略 DefaultIfEmpty()
,将会得到内连接。
采用接受的答案:
from c in categories
join p in products on c equals p.Category into ps
from p in ps.DefaultIfEmpty()
这个语法非常令人困惑,当你想要左连接多个表时,它的工作方式并不清楚。
注意
需要注意的是,from alias in Repo.SomeTable.Where(condition).DefaultIfEmpty()
与 outer-apply/left-join-lateral 是相同的,只要你不引入每行值(也称为实际外部应用),任何(良好的)数据库优化器都可以将其转换为左连接。在 Linq-2-Objects 中请勿这样做,因为那里没有数据库优化器......
详细示例
var query2 = (
from users in Repo.T_User
from mappings in Repo.T_User_Group
.Where(mapping => mapping.USRGRP_USR == users.USR_ID)
.DefaultIfEmpty()
from groups in Repo.T_Group
.Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)
.DefaultIfEmpty()
select new
{
UserId = users.USR_ID
,UserName = users.USR_User
,UserGroupId = groups.ID
,GroupName = groups.Name
}
);
var xy = (query2).ToList();
在与LINQ to SQL一起使用时,它会很好地转换为以下非常易读的SQL查询:
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
我再次强调,如果你在Linq-2-Objects中进行这个操作(而不是Linq-2-SQL),你应该以传统的方式来做(因为LINQ to SQL可以正确地将其转换为连接操作,但在对象上使用这种方法会强制进行全表扫描)。
以下是以实际左连接方式进行操作的传统方法:
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
}
);
现在,以一个更复杂的查询为例,如果你需要进一步解释它是如何工作的,请考虑以下的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
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
)
);
以下是生成的LINQ代码:
(这是DB上下文,dump是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();