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' |
前端渲染如下:
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系统下SQL Server数据库镜像配置全流程详解
· 现代计算机视觉入门之:什么是视频
· 你所不知道的 C/C++ 宏知识
· 聊一聊 操作系统蓝屏 c0000102 的故障分析
· SQL Server 内存占用高分析
· 盘点!HelloGitHub 年度热门开源项目
· DeepSeek V3 两周使用总结
· 02现代计算机视觉入门之:什么是视频
· C#使用yield关键字提升迭代性能与效率
· 回顾我的软件开发经历(1)