EntityFramework - virtual ICollection query

Question

I have entities like this:

public class Student
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }

    //Foreign key for Standard
    public int StandardId { get; set; }
    public virtual Standard Standard { get; set; }
}

public class Standard
{
    public int StandardId { get; set; }
    public string StandardName { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}

In my query, when I write:

dbcontext.Standards.Where(standard => standard.StandardId > 6)

I can see that the underlying query that has been run is something like:

SELECT "Extent1"."StandardId", "Extent1"."StandardName"
FROM "dbo"."Standard" as "Extent1"
WHERE "Extent1"."StandardId" > @EntityKeyValue1;

I understand that because it is IQueryable, even the Expression in Where function has been translated and is actually run in the database and not in the program.

The Students are not loaded since I have marked the collection as virtual. No I am trying to load them in another query:

var XStandard = dbcontext.Standards.Where(standard => standard.StandardId == 10);
var students = XStandard.Students.Where(student => student.StudentId > 5);

I see that the query executed is:

SELECT "Extent1"."StudentId", "Extent1"."StudentName", "Extent1"."StandardId"
FROM "dbo"."Student" as "Extent1"
WHERE "Extent1"."StandardId = @EntityKeyValue1;

I do not see any WHERE clause in that query which checks is the student id is greater than 5. It is actually fetching all the students and filtering out in memory. I understand that this is because it is IEnumerable and not IQueryable and since IEnumerable is LINQ-to-objects, the filtering is done in memory.

When I run the query against dbcontext like this:

var students = dbcontext.Students.Where(student => student.StudentId > 5 && student.StandardId == 10);

Now the SQL query is like:

SELECT "Extent1"."StudentId", "Extent1"."StudentName", "Extent1"."StandardId"
FROM "dbo"."Student" as "Extent1"
WHERE "Extent1"."StandardId" = @EntityKeyValue1 AND "Extent1"."StudentId" > @EntityKeyValue2;

If the query on nested collections fetches all the records rather than a few, whats the point of having nested collections in a one-many relation? Is there a way how I can make sure this is run at the database end? Am I missing something? Please help me understand how EF works internally. Thanks in advance.

Have you turned off Lazy Loading? Notice that `dbcontext.Standards.Where(standard => standard.StandardId > 6)` does **not** execute a query, only declares it. Unless you have turned off Lazy Loading, using `dbcontext.Standards.Where(standard => standard.StandardId > 6).FirstOrDefault();` should load the Students collection — Camilo Terevinto, Apr 12 '19 at 17:34
I have explicitly turned on lazy loading. But when I try to access property, the query is fetching entire collection and doing the filtering in the program instead of doing it on the database end. So what if there are 500 Students, where I want only a dozen records after applying the filter. — Raghavendra, Apr 13 '19 at 05:47

Mikhail 89 · Accepted Answer · 2021-02-19T11:47:30.687

Faced same question. I think what you need here is to load nested collection explicitly.

dbcontext.Entry(XStandard).Collection(c => c.Students).Query().Where(student => student.StudentId > 5).Load();

After that, condition inside Where clause should be translated into SQL.

https://learn.microsoft.com/en-us/ef/ef6/querying/related-data?redirectedfrom=MSDN#Applying-filters-when-explicitly-loading-related-entities

But you definitely need to retrieve that filtered collection, so as mentioned in docs:

When using the Query method it is usually best to turn off lazy loading for the navigation property. This is because otherwise the entire collection may get loaded automatically by the lazy loading mechanism either before or after the filtered query has been executed.

score -1 · Answer 2 · answered Apr 12 '19 at 18:08

-1

You're using lazy loading. In case of lazy loading, related objects (child objects) are not loaded automatically with its parent object until they are requested. By default LINQ supports lazy loading. Maybe this will help you decide: Lazy Loading vs Eager Loading

answered Apr 12 '19 at 18:08

DimaS

37
5

If you understand my question correctly, I want lazy loading. When I want the collection I want it to be filtered and queried on the database end, but in this case, the entire nested is first queried and then filtering is performed in-memory. – Raghavendra Apr 15 '19 at 15:17

EntityFramework - virtual ICollection query

2 Answers2