Entity Framework: Fetch multiple collections with filters applied

Question

Giving the next model:

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

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.

Can you help me? Thanks!

I think you're still retrieving the full dataset because you haven't removed the virtual keyword http://stackoverflow.com/a/20358097/150342 — Colin, Dec 11 '13 at 16:47

Colin · Accepted Answer · 2013-12-12T14:38:57.217

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
    }

And you call it like this:

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

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.

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

Yes, but in that way, it runs one query for retrieving the projects, and another query for retrieving the exams, doesn't it? — ascherman, Dec 11 '13 at 17:28
That is true. I think you need to rethink your technique of partially populating the navigation properties. See my edit — Colin, Dec 12 '13 at 10:08
You are the best @Colin! You've solved the problem! Now i can do it with only one query! Thank you so much buddy! — ascherman, Dec 12 '13 at 13:52
I've removed the "includes" and it works too. Is there any difference?, i mean, instead of context.Foos.Include(x => x.Bars).Include(y => y.Bazs), i've done context.Foos. But within the select i get the collections and it seems like there is no difference. Am i wrong? — ascherman, Dec 12 '13 at 14:29
@ArielScherman have you removed the virtual keyword to prevent lazy loading? The difference will be in the sql. Check it by calling `query.ToString()` — Colin, Dec 12 '13 at 14:37
I've not removed the virtual keyword... but i'm using a DTO as you recommended. So, in that case, what difference is using the include or not? The query seems the same — ascherman, Dec 12 '13 at 14:43
Try removing the `virtual` keywords and see if the sql is different. I would expect lazy loading to load all the projects while include would filter them within the sql...but I could be wrong. — Colin, Dec 12 '13 at 14:51
It seems you can keep the `virtual` keyword if you disable lazy loading via `DbContext.Configuration.LazyLoadingEnabled`. — Sinjai, Feb 20 '19 at 19:26

score 0 · Answer 2 · answered Dec 11 '13 at 17:00

0

what about:

var q = context.Set<Project>().Where(x => x.Current && x.User.Exams.Where(y => y.score > 4).Count() > 0)

or may be

var q = context.Set<Project>().Where(x => x.Current && x.User.Exams.Any(y => y.score > 4))

answered Dec 11 '13 at 17:00

tschmit007

7,559
2
35
43

Entity Framework: Fetch multiple collections with filters applied

2 Answers2

Linked