Entity Framework中使用Linq做Left join 查询

这个查询比较方便,几行代码搞定了很多事情,因此推荐下:

请注意红色和蓝色标记的重要部分.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
(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语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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'

  

前端渲染如下:

posted @   devs  阅读(319)  评论(0编辑  收藏  举报
编辑推荐:
· Linux系统下SQL Server数据库镜像配置全流程详解
· 现代计算机视觉入门之:什么是视频
· 你所不知道的 C/C++ 宏知识
· 聊一聊 操作系统蓝屏 c0000102 的故障分析
· SQL Server 内存占用高分析
阅读排行:
· 盘点!HelloGitHub 年度热门开源项目
· DeepSeek V3 两周使用总结
· 02现代计算机视觉入门之:什么是视频
· C#使用yield关键字提升迭代性能与效率
· 回顾我的软件开发经历(1)
点击右上角即可分享
微信分享提示