Entity Framework filter nested collection

Question

Entity Framework filter nested collection

c# entity-framework entity-framework-6 linq

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?

Praveen Prasannan

Popular Answer

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.

Manish Kumar

View more on Stack Overflow

Chris Wyatt · Accepted Answer · 11/13/2015 1:57:06 PM

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

Entity Framework filter nested collection

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related