Entity Framework filter nested collection

Question

I have a entity relation diagram as follows.

ClassEntity:

public int id
public int std
public virtual ICollection<StudentEntity> students

StudentEntity:

public int id
public string name
public string gender
public virtual ClassEntity class
public virtual StudentAddressEntity studentAddress

StudentAddressEntity:

public int id
public string address

I need to get the class and its male children.

var classEntity = dbContext.Set<ClassEntity>().Where(t => t.id == classId);
var query = classEntity.Include(c => c.students.Select(s => s.studentAddress))
           .FirstOrDefault(c => c.students.Any(s => s.gender == GenderEnum.Male));

But it is returning the class with all the students. How to filter only male students?

Possible duplicate of [How to filter nested collection Entity Framework objects?](http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects) — DavidG, Nov 13 '15 at 13:26
Yeah. I referred that already. But it didn't provide me an answer — Praveen Prasannan, Nov 13 '15 at 13:28
Supported in EF core 5: https://stackoverflow.com/a/61147681/861716 — Gert Arnold, Jul 26 '21 at 07:00

score 3 · Answer 1 · edited May 26 '21 at 08:43

You intentionally "can't" do this directly with the EF proxies. For example, consider what would happen when you tried to call SaveChanges() and all of the female students are missing from ClassEntity.Students!

Instead, the usual thing to do if you're just displaying data is to project onto an anonymous type or a DTO, e.g.:

var classOnlyMale = dbContext.Set<ClassEntity>()
    .Where(x => x.Id == classId)
    .Select(x => new // I'm using an anonymous type here, but you can (and usually should!) project onto a DTO instead
    {
        // It's usually best to only get the data you actually need!
        Id = x.Id
        Students = x.Students
            .Where(y => y.Gender == GenderEnum.Male)
            .Select(y => new { Name = y.Name, ... })
    });

Or, if you desperately need to make changes and save them:

var classOnlyMale = dbContext.Set<ClassEntity>()
    .Where(x => x.Id == classId)
    .Select(x => new
    {
        Class = x,
        MaleStudents = x.Students.Where(y => y.Gender == GenderEnum.Male)
    });

I quite strongly recommend the former unless there's no way around it. It's really easy to introduce bugs if you're making changes to filtered data and trying to save it.

score 3 · Answer 2 · answered Nov 13 '15 at 15:30

I have used joins to accomplish similar results in the past. For eg I've accounts that have addresses nested (1:M). If I want to get, say, all the accounts that belong to a particular country, I would use joins as below:

(from a in accountRepo.GetAll()
      join aa in accountAddressRepo.GetAll() on a.AccountId equals aa.AccountId
      join ad in addressRepo.GetAll() on aa.AddressId equals ad.AddressId
      where ad.CountryId == codeCountryId
      select a).ToList();

If you are not using repository pattern you can simply replace accountRepo.GetAll() with DbContext.Set().

In your case you should be able to join Student, Address and Class entities and get similar results. Something like below should work for you:

(from s in DbContext.Set<StudentEntity>
  join a in DbContext.Set<StudentAddressEntity> on s.studentAddress.id equals a.id
  join c in DbContext.Set<ClassEntity> on s.class.id equals c.id
  where c.std == classId && s.gender== GenderEnum.Male
  select s).ToList();

please note this is a simple representation based on my understanding of your database and entity names. You may need to tweak this query a bit to make it compilable but the underlying idea should work for you. Please let me know how did it work for you.

Chris Wyatt · Accepted Answer · 2015-11-13T13:57:06.630

2

The below should load only the male students for each class.

var classEntity = testContext.Set<ClassEntity>().Where(t => t.Id == classId);
var classes = classEntity.ToList().Select(c =>
{
    testContext.Entry(c)
    .Collection(p => p.Students)
    .Query()
    .Where(s => s.Gender == GenderEnum.Male)
    .Load();

    return c;
});

edited Nov 13 '15 at 13:57

answered Nov 13 '15 at 13:29

Chris Wyatt

142
8

Keep in mind that this method will filter the students on the client side, all the students will still come across the wire from the database before only males are selected. – Bradley Uffner Nov 13 '15 at 14:34
According to http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx it would seem that this is applied at the database end. – Chris Wyatt Nov 13 '15 at 14:45
Calling `.ToList` will always bring the entities local. Any operation performed on the result of `.ToList` won't happen on the database. I've scanned the page you linked, but I don't know which section you are referring to specifically. If I'm wrong about this behavior I'd love to know about it. – Bradley Uffner Nov 13 '15 at 14:49
Ahh, never mind, I originally read your code incorrectly. You are correct that the student operations will happen server side. Though it will do a separate student query for each class. I apologize. – Bradley Uffner Nov 13 '15 at 14:52
It's a fair point though. Re-reading my own code it looks like this will execute N separate queries which under other circumstances wouldn't be ideal. In this case t.Id is likely to be unique and classes should only ever contain 0 or 1 elements so in this case it's acceptable. – Chris Wyatt Nov 13 '15 at 14:56

score 0 · Answer 4 · edited Jul 26 '21 at 05:49

0

I think join is the right way to go about it as suggested by Manish Kumar.

(from s in DbContext.Set<StudentEntity>
  join a in DbContext.Set<StudentAddressEntity> on s.studentAddress.id equals a.id
  join c in DbContext.Set<ClassEntity> on s.class.id equals c.id
  where c.std == classId && s.gender== GenderEnum.Male
  select s).ToList();

edited Jul 26 '21 at 05:49

Glorfindel

21,988
13
81
109

answered Nov 13 '15 at 21:10

Shoeb Siddique

155
2
8

Entity Framework filter nested collection

4 Answers4