如何在Linq to sql中進行left join

可以用 into 語法，配合 DefaultIfEmpty() 方法加載右側表中的空值。

例子：

var query =
from r in db.Roles
join sr in
(
from ss in db.StaffInRoles
where ss.StaffId == staffId
select ss
)
on r.Id equals sr.RoleId
into x
from y in x.DefaultIfEmpty()
select new RoleInfo
{
RoleId = r.Id,
RoleName = r.Name,
IsSet = y != null
};

注意這裡最後可以通過 y != null 來判斷右值是否為空。

這個 Linq to sql 語句生成的對應 SQL 為：

exec sp_executesql N'SELECT [t0].[Id] AS [RoleId], [t0].[Name] AS [RoleName],
(CASE
WHEN [t2].[test] IS NOT NULL THEN 1
ELSE 0
END) AS [IsSet]
FROM [Role] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[StaffId], [t1].[RoleId]
FROM [StaffInRole] AS [t1]
) AS [t2] ON ([t0].[Id] = [t2].[RoleId]) AND ([t2].[StaffId] = @p0)',N'@p0 int',@p0=1
over.