Entity Framework中使用Linq做Left join 查询
这个查询比较方便,几行代码搞定了很多事情,因此推荐下:
请注意红色和蓝色标记的重要部分.
(from nav in DbQuery.Navigations
join unp in DbQuery.UserNaviPermissions
on new {nav.OrganizationId, NavigationId = nav.Id, UserId = dto.UserOrRoleId}
equals new {unp.OrganizationId, unp.NavigationId, unp.UserId } into unp_join
from unp in unp_join.DefaultIfEmpty()
orderby
nav.ParentId,
nav.OrderId
select new PermissionMapOutDto
{
IsChecked = unp != null && unp.IsGranted,
Id = unp == null ? Guid.NewGuid() : unp.Id,
NavigationId = nav.Id,
Name = nav.Name,
DisplayName = nav.DisplayName,
ImageSource = nav.Icon,
ParentId = nav.ParentId,
OrderId = nav.OrderId,
Actions = (from act in DbQuery.Actions
join uap in DbQuery.UserActionPermissions
on new {act.Id, act.OrganizationId, act.NavigationId, UserId = dto.UserOrRoleId}
equals new {Id = uap.ActionId, uap.OrganizationId, NavigationId = nav.Id, uap.UserId } into uap_join
from uap in uap_join.DefaultIfEmpty()
orderby
act.OrderId
select new ActionMapOutDto
{
Id = uap == null ? Guid.NewGuid() : uap.Id,
ActionId = act.Id,
DisplayName = act.DisplayName,
OrderId = act.OrderId,
IsChecked = uap != null && uap.IsGranted
})
.ToList(),
}).ToList();
执行SQL语句如下:
exec sp_executesql N'SELECT
[Project1].[OrganizationId] AS [OrganizationId],
[Project1].[OrganizationId1] AS [OrganizationId1],
[Project1].[Id2] AS [Id],
[Project1].[OrderId] AS [OrderId],
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
[Project1].[Id] AS [Id1],
[Project1].[Name] AS [Name],
[Project1].[DisplayName] AS [DisplayName],
[Project1].[Icon] AS [Icon],
[Project1].[ParentId] AS [ParentId],
[Project1].[C5] AS [C3],
[Project1].[OrderId1] AS [OrderId1],
[Project1].[C3] AS [C4],
[Project1].[Id1] AS [Id2],
[Project1].[DisplayName1] AS [DisplayName1],
[Project1].[C4] AS [C5]
FROM ( SELECT
[Extent1].[OrganizationId] AS [OrganizationId],
[Extent1].[Id] AS [Id],
[Extent1].[ParentId] AS [ParentId],
[Extent1].[Name] AS [Name],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[Icon] AS [Icon],
[Extent1].[OrderId] AS [OrderId],
CASE WHEN (( NOT (([Extent2].[OrganizationId] IS NULL) AND ([Extent2].[Id] IS NULL))) AND ([Extent2].[IsGranted] = 1)) THEN cast(1 as bit) WHEN ( NOT (( NOT (([Extent2].[OrganizationId] IS NULL) AND ([Extent2].[Id] IS NULL))) AND ([Extent2].[IsGranted] = 1))) THEN cast(0 as bit) END AS [C1],
CASE WHEN (([Extent2].[OrganizationId] IS NULL) AND ([Extent2].[Id] IS NULL)) THEN NEWID() ELSE [Extent2].[Id] END AS [C2],
[Join2].[Id1] AS [Id1],
[Join2].[DisplayName] AS [DisplayName1],
[Join2].[OrderId] AS [OrderId1],
CASE WHEN ([Join2].[OrganizationId1] IS NULL) THEN CAST(NULL AS uniqueidentifier) WHEN (([Join2].[OrganizationId2] IS NULL) AND ([Join2].[Id2] IS NULL)) THEN NEWID() ELSE [Join2].[Id2] END AS [C3],
CASE WHEN ([Join2].[OrganizationId1] IS NULL) THEN CAST(NULL AS bit) WHEN (( NOT (([Join2].[OrganizationId2] IS NULL) AND ([Join2].[Id2] IS NULL))) AND ([Join2].[IsGranted] = 1)) THEN cast(1 as bit) WHEN ( NOT (( NOT (([Join2].[OrganizationId2] IS NULL) AND ([Join2].[Id2] IS NULL))) AND ([Join2].[IsGranted] = 1))) THEN cast(0 as bit) END AS [C4],
[Extent2].[OrganizationId] AS [OrganizationId1],
[Extent2].[Id] AS [Id2],
CASE WHEN ([Join2].[OrganizationId1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C5]
FROM [dbo].[WiseNavigations] AS [Extent1]
LEFT OUTER JOIN [dbo].[WiseUserNaviPermissions] AS [Extent2] ON (((CASE WHEN ([Extent2].[ExpireTime] IS NULL) THEN convert(datetime2, ''9999-12-31 23:59:59.9999999'', 121) ELSE [Extent2].[ExpireTime] END) > @DynamicFilterParam_000001) ) AND (([Extent2].[OrganizationId] = @DynamicFilterParam_000007) ) AND ([Extent1].[OrganizationId] = [Extent2].[OrganizationId]) AND ([Extent1].[Id] = [Extent2].[NavigationId]) AND (@p__linq__0 = [Extent2].[UserId])
LEFT OUTER JOIN (SELECT [Extent3].[OrganizationId] AS [OrganizationId1], [Extent3].[Id] AS [Id1], [Extent3].[NavigationId] AS [NavigationId], [Extent3].[DisplayName] AS [DisplayName], [Extent3].[OrderId] AS [OrderId], [Extent4].[OrganizationId] AS [OrganizationId2], [Extent4].[Id] AS [Id2], [Extent4].[IsGranted] AS [IsGranted]
FROM [dbo].[WiseActions] AS [Extent3]
LEFT OUTER JOIN [dbo].[WiseUserActionPermissions] AS [Extent4] ON (((CASE WHEN ([Extent4].[ExpireTime] IS NULL) THEN convert(datetime2, ''9999-12-31 23:59:59.9999999'', 121) ELSE [Extent4].[ExpireTime] END) > @DynamicFilterParam_000001) ) AND (([Extent4].[OrganizationId] = @DynamicFilterParam_000007) ) AND ([Extent3].[Id] = [Extent4].[ActionId]) AND ([Extent3].[OrganizationId] = [Extent4].[OrganizationId]) AND (@p__linq__1 = [Extent4].[UserId]) ) AS [Join2] ON (([Join2].[OrganizationId1] = @DynamicFilterParam_000007) ) AND ([Join2].[NavigationId] = [Extent1].[Id])
WHERE ([Extent1].[OrganizationId] = @DynamicFilterParam_000007)
) AS [Project1]
ORDER BY [Project1].[ParentId] ASC, [Project1].[OrderId] ASC, [Project1].[OrganizationId] ASC, [Project1].[OrganizationId1] ASC, [Project1].[Id2] ASC, [Project1].[Id] ASC, [Project1].[C5] ASC, [Project1].[OrderId1] ASC',N'@DynamicFilterParam_000001 datetime2(7),@DynamicFilterParam_000002 bit,@DynamicFilterParam_000007 uniqueidentifier,@DynamicFilterParam_000008 bit,@p__linq__0 uniqueidentifier,@p__linq__1 uniqueidentifier',@DynamicFilterParam_000001='2020-05-03 21:36:59.0489122',@DynamicFilterParam_000002=NULL,@DynamicFilterParam_000007='AA504841-748E-4655-9B87-9C46D0511F54',@DynamicFilterParam_000008=NULL,@p__linq__0='E74EA87B-9FDE-4EB1-B065-1E7DA6A50FE7',@p__linq__1='E74EA87B-9FDE-4EB1-B065-1E7DA6A50FE7'
前端渲染如下:
