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?

1
1
11/13/2015 1:47:26 PM

Accepted Answer

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;
});
2
11/13/2015 1:57:06 PM

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.



Related Questions





Related

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