postgresql - ef core 5 多对多过滤器

Question

这是我的查询

 public async Task<IEnumerable<Menu>> GetMenuByRolesAsync(string[] roles)
        {
    var result= await _context.Menus//.Include(o => o.Parent)
                                     .Include(m => m.Childrens)
                                     .ThenInclude(m => m.Childrens)
                                     .Include(m => m.Roles.Where(r => roles.Contains(r.Name)))   --it is not filtering basd on roles                          
                                     .Where(m => m.ParentId == null)
                                     .ToListAsync();
}

正在生成以下查询

-- @__roles_0='System.String[]' (DbType = Object)
SELECT m.id, m.icon, m.name, m.parent_id, m.url, t.role_id, t.menu_id, t.id, t.concurrency_stamp, t.name, t.normalized_name
FROM security.menu AS m
LEFT JOIN (
    SELECT r.role_id, r.menu_id, r0.id, r0.concurrency_stamp, r0.name, r0.normalized_name
    FROM security.role_menu AS r
    INNER JOIN security.role AS r0 ON r.role_id = r0.id
    WHERE r0.name = ANY (@__roles_0) OR ((r0.name IS NULL) AND (array_position(@__roles_0, NULL) IS NOT NULL))
) AS t ON m.id = t.menu_id
WHERE (m.parent_id IS NULL)
ORDER BY m.id, t.role_id, t.menu_id, t.id

这是多对多配置//多对多

    builder.HasMany(r => r.Menus)
           .WithMany(r => r.Roles)
           .UsingEntity<RoleMenu>(
              j => j
                  .HasOne(rm => rm.Menu)
                  .WithMany(m => m.RoleMenus)
                  .HasForeignKey(rm => rm.MenuId),
              j => j
                  .HasOne(rm => rm.Role)
                  .WithMany(r => r.RoleMenus)
                  .HasForeignKey(rm => rm.RoleId),
              j =>
              {
                  j.ToTable("role_menu", schema: "security");                                                 
                  j.HasKey(rm => new { rm.RoleId, rm.MenuId });
              });

我需要根据角色过滤菜单..但它不是根据角色过滤..正在获取所有角色..我检查了生成的查询..请告诉我问题是什么..

Answer 1

您正在混合过滤包含和实体过滤。

过滤包含

.Include(m => m.Roles.Where(r => roles.Contains(r.Name)))

仅过滤相关集合中的项目(在本例中为菜单角色)。

为了过滤实体集(在本例中为菜单)，您需要将其替换为通常的 Where oparator，对于所需的过滤将是

.Where(m => m.Roles.Any(r => roles.Contains(r.Name)))