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 stackoverflow.com/a/20358097/150342 — Colin
– Colin, Commented Dec 11, 2013 at 16:47

Colin · Accepted Answer · 2013-12-12 14:38:57Z

1

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

edited Dec 12, 2013 at 14:38

answered Dec 11, 2013 at 17:10

Colin

22.7k17 gold badges111 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ascherman Over a year ago

Yes, but in that way, it runs one query for retrieving the projects, and another query for retrieving the exams, doesn't it?

Colin Over a year ago

That is true. I think you need to rethink your technique of partially populating the navigation properties. See my edit

ascherman Over a year ago

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 Over a year ago

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?

Colin Over a year ago

@ArielScherman have you removed the virtual keyword to prevent lazy loading? The difference will be in the sql. Check it by calling query.ToString()

|

tschmit · Accepted Answer · 2013-12-11 17:00:33Z

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, 2013 at 17:00

tschmit

7,8402 gold badges38 silver badges45 bronze badges

Collectives™ on Stack Overflow

Entity Framework: Fetch multiple collections with filters applied

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related