EF核心5多对多过滤器 [英] Ef core 5 many to many filter

问题描述

这是我的查询

 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

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

This is Many to many configuration // many to many

    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 });
              });

我需要根据角色过滤菜单..但是，不是根据角色进行过滤..我正在获取所有角色..我检查了生成的查询..请让我知道问题出在哪里..

i need to filter menus based on roles..But it is not filtering based on roles..Am getting all the roles..I checked generated query..Please let me know what is the issue..

推荐答案

您正在将过滤的包含与实体过滤混合在一起.

You are mixing filtered include with entity filtering.

已过滤的包含

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

仅过滤相关集合中的项目(在这种情况下为菜单角色).

just filters the items in the related collection (menu roles in this case).

为了过滤实体集(在这种情况下为菜单)，您需要将其替换为通常的 Where 遮盖器，用于所需过滤的将是

In order to filter the entity set (menus in this case), you need to replace it with the usual Where oparator, which for the desired filtering will be

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

这篇关于EF核心5多对多过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！