Entity Framework filter nested collection

c# entity-framework entity-framework-6 linq


I have the following entity relationship diagram.

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 must obtain the class and the male students.

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

However, it is bringing back the entire class. How can I just filter male students?

11/13/2015 1:47:26 PM

Accepted Answer

11/13/2015 1:57:06 PM

Popular Answer

In the past, I have used joins to achieve comparable results. For instance, I have accounts with nested addresses (1:M). I would use joins like shown below if I wanted to obtain, example, all the accounts that are associated with a specific nation.

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

DbContext.Set can easily be used in place of accountRepo.GetAll() if you are not utilizing the repository style ().

You should be able to link the Student, Address, and Class entities in your situation and obtain outcomes that are similar. The following 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 be aware that this is only a basic illustration based on my knowledge of your database and entity names. The fundamental concept should work for you even if you need to make a few minor adjustments to this query to make it compilable. Please share your experience with me.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow