I have the following entity relationship diagram.
public int id public int std public virtual ICollection<StudentEntity> students
public int id public string name public string gender public virtual ClassEntity class public virtual StudentAddressEntity studentAddress
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?
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.