sql - Linq to Entities/SQL 中的左外连接

如何在 LINQ to Entities 中编写以下 SQL？

SELECT r.rolename,
       ( CASE
           WHEN ur.username IS NULL THEN 0
           ELSE 1
         END ) AS isinrole
FROM   bgt.roles r
       LEFT OUTER JOIN bgt.usersinroles ur
         ON ur.rolename = r.rolename
            AND ur.username = 'ADMIN'

最佳答案

这对我有用。感谢所有的建议。

var query =
from r in Roles
from ur in UsersInRoles
.Where(v => v.Rolename == r.Rolename && v.Username == "ADMIN")
.DefaultIfEmpty()
select new { Rolename = r.Rolename, IsInRole = (ur.Username != null) };

生成的SQL如下

SELECT
1 AS [C1],
[Extent1].[Rolename] AS [Rolename],
CASE WHEN ([Extent2].[Username] IS NOT NULL) THEN cast(1 as bit) WHEN ([Extent2].[Username] IS NULL) THEN cast(0 as bit) END AS [C2]
FROM  [bgt].[Roles] AS [Extent1]
LEFT OUTER JOIN [bgt].[UsersInRoles] AS [Extent2] ON ([Extent2].[Rolename] = [Extent1].[Rolename]) AND ('ADMIN' = [Extent2].[Username])

关于sql - Linq to Entities/SQL 中的左外连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6321941/