EntityFramework - virtual ICollection query

c# entity-framework entity-framework-6 sql

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.

1
0
4/12/2019 5:34:13 PM

Popular Answer

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

0
4/12/2019 6:08:13 PM


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