实体框架：使用过滤器获取多个集合 [英] Entity Framework: Fetch multiple collections with filters applied

问题描述

给下一个模型：

public class User{
    public int IdUser { get; set;}
    public virtual ICollection<Project> Projects { get; set;}
    public virtual ICollection<Exam> Exams { get; set;}
}

public class Project{
    public int IdProject { get; set;}
    public bool Current { get; set;}
    public virtual User { get; set;}
}

public class Exam{
    public int IdExam { get; set;}
    public int score { get; set;}
    public virtual User { get; set;}
}

我需要获得项目 当前= true 和考试，给定用户的分数大于4 。

I need to get the projects with current=true and exams with score greater than 4 from a given user.

当我需要过滤导航属性时，为避免将所有记录和过滤器应用到内存中，我执行下一步：

When i need to filter a navigation property, for avoiding bringing all the records and apply the filter in memory, i do the next:

IQueryable<Project> = context.Entry(user).Collection(x => x.Projects).Query().Where(n => n.Current).ToList();

这样，我从数据库中只提取当前的项目。避免将所有项目检索到内存，然后在内存中应用过滤器的其他方式。

On that way, i bring from the database only the current projects. Avoiding the other way of retrieving all the projects to memory and then applying the filter on memory.

所以现在，我想做同样的事情（只记录重要的记录），但是我不知道如果我有多个集合，我该怎么办？

So now, i want to do the same (bring only the records that matters), but i don't know how can i do that when i have more than one collection.

你能帮我吗？谢谢！

推荐答案

您的存储库将非常快速地填充使用导航属性返回具有不同类型的过滤器的实体的方法。也许你应该有一个这样的方法：

Your repository is going to fill up very rapidly with methods that return entities with different types of filters on the navigation properties. Perhaps you should have a method that looks like this:

   public GetUser(int userid, 
                  Expression<System.Func<Project, System.Boolean>> projectFilter,
                  Expression<System.Func<Exam, System.Boolean>> examFilter)
    {
       var user = context.Users.Find(userid); 

       context.Entry(user)
              .Collection(c => c.Projects)
              .Query()
              .Where(projectFilter)
              .Load(); 

       context.Entry(user)
              .Collection(c => c.Exams)
              .Query()
              .Where(examFilter)
              .Load();

       return user
    }

：

var userincludingCurrentProjectsAndExamsWithScoresLessThan4 = 
                         userRepo.GetUser(id, p => p.Current, e => e.Score > 4)

不要忘了删除虚拟从集合或延迟加载中的关键字将在应用过滤器之前将整个集合从数据库中拉出：

And don't forget to remove the virtual keywords from the collections or lazy loading will pull the whole collection out of the database before the filter is applied:

public class User
{
    public int IdUser { get; set;}
    public ICollection<Project> Projects { get; set;}
    public ICollection<Exam> Exams { get; set;}
}

编辑
I不得不说，这种部分填充导航属性的技术对我来说有点有趣。我认为大多数人做的是将数据投入DTO或ViewModels，以实现您要做的事情。那么DTO可以拥有更有意义的属性名称。这样的东西：

EDIT I have to say that this technique of partially populating navigation properties smells a bit funny to me. I think what most people do is project the data into DTOs or ViewModels to achieve what you are trying to do. Then the DTO's can have property names that make more sense. Something like this:

var query = from f in context.Foos.Include(x => x.Bars).Include(y => y.Bazs)
            select new FooDTO
            {
                ID = f.ID,
                Toms = f.Bars.Where(b => b.Name == "Tom").ToList(),
                Dicks = f.Bazs.Where(b => b.Name == "Dick").ToList()
            };

string sql = query.ToString();//useful in debugger. Remove once satisfied.

但是，如果你真的想投入一个实体，而不是一个DTO，你可以这样做： p>

But if you really want to project into an entity rather than a DTO you could do this:

var usersWithTomsAndDicksOohErr = 
(from f in context.Foos.Include(x => x.Bars).Include(y => y.Bazs)
    select new //anonymous. You can't construct entities using LINQ to Entities
    {
        ID = f.ID,
        Toms = f.Bars.Where(b => b.Name == "Tom").ToList(),
        Dicks = f.Bazs.Where(b => b.Name == "Dick").ToList()
    })
    .AsEnumerable()//moves only the data you want into memory
    .Select(x => new Foo//now you can construct the entity using Linq to Objects
    {
       ID = x.ID,
       Bars = x.Toms,
       Bazs = x.Dicks
    });

这篇关于实体框架：使用过滤器获取多个集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！