在不执行任何查询的情况下，生成子句包含ICOLLECTION和列表之间的比较的地方

Question

我想使用实体框架从表Users过滤行。Users和Roles具有N-N关系：

 public class Users 
{
    public Users()
    {
      Roles = new HashSet<Role>();
    }
    public Int64 Id { get; set; }
    public string UserName { get; set; }
    public virtual ICollection<Roles> Roles { get; set; }
}
 public class Roles
    {
        public Roles()
        {
            Users = new HashSet<Users>();
        }
        public string Id { get; set; }
        public string Name { get; set; }
        public string DisplayName { get; set; }
        public virtual ICollection<Users> Users { get; set; }
    }

和：

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        modelBuilder.Entity<Roles>()
            .HasMany(e => e.Users)
            .WithMany(e => e.Roles)
            .Map(m => m.ToTable("UserRoles")
            .MapLeftKey("RoleId")
            .MapRightKey("UserId"));
            ...............
}

我想基于UserName及其Roles过滤Users。用户数量很大，因此我只想在应用条件和分页的情况下执行查询。

我想找到他们的名称包括输入的UserName（如果不是空或空），并且它们都具有输入Roles列表的所有角色。

我应该使用什么代码来实现这一目标？

public Users FilterUsers(UserSearchDto inputParam, int pageSize, int pageNumber)
{
      var skip = (pageNumber - 1) * pageSize;
      var take = pageSize;
      int total = 0;
      var isNotUserNameFiltered = string.IsNullOrEmpty(inputParam.UserName);
      var isNotRolesFiltered = inputParam.Roles?.Count <= 0; //Roles type is List<string>
      var q = db.Users
                .Include("Roles")
                .AsQueryable();
      q = q.Where(user =>
                (isNotUserNameFiltered || user.UserName.Contains(inputParam.Item.UserName)) &&
                (isNotRolesFiltered // ||  // what should I write here?
                ));
      // get totalcount
      total = q.Count();
      // execute and get result from database
      var data = q.Select(user => new Users
                                  {
                                       Id = user.Id, 
                                       UserName = user.UserName,
                                       Roles = user.Roles.Select(role => role.DisplayName).ToList()
                                  })
                  .Distinct()
                  .OrderBy(u => u.Id)
                  .Skip(skip)
                  .Take(take)
                  .ToList();
}

Answer 1

我应该在这里写什么？

标准方法是使用All方法：

inputParam.Roles.All(roleName => user.Roles.Any(role => role.Name == roleName))

（根据inputParam.Roles中的内容，您可能需要使用role.DisplayName）

但是，在EF6中，如果计算匹配项并与所需集合的计数进行比较，您将获得更好的SQL翻译：

user.Roles.Count(role => inputParam.Roles.Contains(role.Name)) == inputParam.Roles.Count

Answer 2

只是尝试一下;

(isNotRolesFiltered ||  user.Roles.All(r => inputParam.Roles.Contains(r))

Answer 3

您可以简单地将.contains/any/all/etc使用iqueryable中的简单类型集合。Where子句（roles.all.all（...）在下面的代码中）这是带有用户名/角色过滤器的抽象代码（基于您的）：

var skip = (pageNumber - 1) * pageSize;
var take = pageSize;
var query = db.Users
    .AsNoTracking()
    .Include("Roles")
    .AsQueryable();
if (!string.IsNullOrEmpty(username))
{
    var username = inputParam.UserName.ToLower();
    query = query.Where(u => u.UserName.ToLower().Contains(username));
}
if (inputParam.Roles?.Count > 0)
{
    var roles = db.Roles
        .AsNoTracking()
        .Where(r => inputParam.Roles.Contains(r.Name))
        .Select(r => r.Id)
        .ToList();
    query = query.Where(u => roles.All(inputRole => u.Roles.Any(userRole => userRole.Id == inputRole)));
}
var total = query.Count();
var result = query
    .Skip(skip)
    .Take(take)
    .OrderBy(u => u.Id)
    .Select(...)
    .ToList();

• 第8-12行 - 准备查询以通过用户名过滤（仅在以防万一用户名不是null而不是空名）
• 第14-22行 - 准备查询以通过角色过滤
• 第14行 - 获取过滤用户的总数
• 第25-30行 - 仅将用户用于特定页面，然后将其转换为DTO

注意：

1. QUERY在您未调用.aseNumerable（）或.tolist（）之前才执行查询（）方法。
2. 我预加载了角色ID，因为SQL Server应该比角色（string/nvarchar）之间的比较速度（int）更快地进行比较和比较。第19-23行