EF linq分组查询结果再条件筛选

微软有关linq查询的group分组查询表达式参考
https://docs.microsoft.com/zh-cn/dotnet/csharp/linq/group-query-results
https://docs.microsoft.com/zh-cn/dotnet/csharp/language-reference/keywords/group-clause
https://docs.microsoft.com/zh-cn/previous-versions/dotnet/netframework-4.0/bb896250(v=vs.100)

group 子句返回一个 IGrouping<TKey,TElement> 对象序列，这些对象包含零个或更多与该组的键值匹配的项。 例如，可以按照每个字符串中的第一个字母对字符串序列进行分组。 在这种情况下，第一个字母就是键，类型为 char，并且存储在每个 IGrouping<TKey,TElement> 对象的 Key 属性中。 编译器可推断键的类型。

 

/// <summary>
/// 根据一个权限id查询有此权限的所有用户，并区别是党群还是门户审核人
/// </summary>
/// <param name="functionId">权限id</param>
/// <param name="menOrDangFunctionId">门户或党群权限值</param>
/// <returns></returns>
public IQueryable<AnUserView> GetQueryUserListByFuncitonIdAndMenOrDangQun(string functionId,string menOrDangFunctionId)
{
	var query = from u in dbContext.AnUser
				let roleIds = (from r in dbContext.RoleFunctions
							   where r.FuncitonId == functionId || r.FuncitonId == menOrDangFunctionId
							   group r by r.RoleId into gp1
							   where gp1.Count() >= 2
							   select gp1.Key)                    
				where roleIds.ToList().Contains(u.RoleId)
				join dt in dbContext.Organization on u.DepartmentId equals dt.Id into km
				from d in km.DefaultIfEmpty()
				select new AnUserView()
				{
					Id = u.Id,
					Account = u.Account,
					AuthCode = u.AuthCode,
					CreationTime = u.CreationTime,
					Creator = u.Creator,                  
					DepartmentId = u.DepartmentId,
					DepartmentName = u.DepartmentName,
					OrganizationName = d.OrganizationName,
					Email = u.Email,                  
					RealName = u.RealName,
					RoleId = u.RoleId,
					Sex = u.Sex,
					Status = u.Status,
					UserNo = u.UserNo
				};
	return query;
}

 EF编译器解析的生成的SQL:

SELECT
    1 AS [C1],
    [Project1].[Id] AS [Id],
    [Project1].[Account] AS [Account],
    [Project1].[AuthCode] AS [AuthCode],
    [Project1].[CreationTime] AS [CreationTime],
    [Project1].[Creator] AS [Creator],
    [Project1].[DepartmentId] AS [DepartmentId],
    [Project1].[DepartmentName] AS [DepartmentName],
    [Project1].[OrganizationName] AS [OrganizationName],
    [Project1].[Email] AS [Email],
    [Project1].[RealName] AS [RealName],
    [Project1].[RoleId] AS [RoleId],
    [Project1].[Sex] AS [Sex],
    [Project1].[Status] AS [Status],
    [Project1].[UserNo] AS [UserNo]
    FROM ( SELECT
        @p__linq__0 AS [p__linq__0],
        @p__linq__1 AS [p__linq__1],
        [Extent1].[Id] AS [Id],
        [Extent1].[RealName] AS [RealName],
        [Extent1].[UserNo] AS [UserNo],
        [Extent1].[Sex] AS [Sex],
        [Extent1].[Status] AS [Status],
        [Extent1].[RoleId] AS [RoleId],
        [Extent1].[DepartmentId] AS [DepartmentId],
        [Extent1].[Account] AS [Account],
        [Extent1].[CreationTime] AS [CreationTime],
        [Extent1].[Creator] AS [Creator],
        [Extent1].[DepartmentName] AS [DepartmentName],
        [Extent1].[Email] AS [Email],
        [Extent1].[AuthCode] AS [AuthCode],
        [Extent2].[OrganizationName] AS [OrganizationName]
        FROM  [dbo].[AnUser] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Organization] AS [Extent2] ON [Extent1].[DepartmentId] = [Extent2].[Id]
    )  AS [Project1]
    WHERE  EXISTS (SELECT
        1 AS [C1]
        FROM ( SELECT
            [Extent3].[RoleId] AS [K1],
            COUNT(1) AS [A1]
            FROM [dbo].[RoleFunctions] AS [Extent3]
            WHERE [Extent3].[FuncitonId] IN (@p__linq__0,@p__linq__1)
            GROUP BY [Extent3].[RoleId]
        )  AS [GroupBy1]
        WHERE ([GroupBy1].[A1] >= 2) AND ([GroupBy1].[K1] = [Project1].[RoleId])
    )