3

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?

Improve this question
3

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

Comments

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;
});
Improve this answer

5 Comments

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.
According to blogs.msdn.com/b/adonet/archive/2011/01/31/… it would seem that this is applied at the database end.
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.
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.
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.
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();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.