CODEWITHSUNDEEP
c#linqentity-framework-6navigation-properties
Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34244

Does EF6 virtual navigation property result into SQL query?

Assuming that I have an entity with virtual nagivation property like this:

public class School
{
    public long Id { get; set; }

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

As I understand, EF6 uses proxy to enable lazy-loading of Students, but do the following LINQ queries:

var myStudent = this.Students.Single(x => x.Id == id);
var studentsCount = this.Students.Count();
var bestStudents = this.Students
    .OrderByDescending(x => x.GPA)
    .Take(5)
    .ToArray();

result into a SQL query (just like IQueryable<T> does)? Or is it just a lazy-loading collection and will load all students into memory after the first request, and then perform simple IEnumerable<T> LINQ behaviour?

Upvotes: 1

Views: 950

Answers (3)

Bradley Uffner
Bradley Uffner

Reputation: 17001

When you query for an entity in Entity Framework, the objects that get returned are not (always) the type of object you think they are. Behind the scenes, it creates a brand new class that inherits from the your class. Because OOP allows a subclass to be stored in a variable typed as the superclass, you never really notice. This is the "proxy" that you mention. That's why the virtual function allows lazy loading. The subclass overrides your virtual method, and contains the code to lazy load the extra data before returning it.

That overridden property call will then check the context to see if the navigation properties are already loaded. If they are, it just returns them. If they are not, it will make additional SQL calls to load them, storing them in the DbContext for later.

In your updated question, my understanding is that running those lines of code would result in 3 separate queries being executed.

Upvotes: 1

Anytoe
Anytoe

Reputation: 1675

Assuming that the second code block is executed somewhere INSIDE the scope of an instance of 'School' (so 'this' is an instance of 'School' - in my example below school19) then the following scenarios are possible:

A) You have loaded your instance of 'School' like this (Lazy loading enabled):

var school19 = dbContext.Set<School>()
 .FirstOrDefault(school => school.Id == 19)

Then your 3 lines of code for accessing the property 'Students' will trigger a single additional database hit when 

var myStudent = this.Students.Single(x => x.Id == id);

is executed, but no more database hits will occur with the subsequent two statements.

B) In case you have loaded your instance of 'School' like this (Lazy loading enabled):

var school19 = dbContext.Set<School>()
 .Include(school => school.Students)
 .FirstOrDefault(school => school.Id == 19)

Then your 3 lines of code for accessing the property 'Students' will not trigger an additional database hit.

C) If lazy loading was disabled, then

  • A) would result in a Null Reference Exception
  • B) would behave the same

As a last remark, if 'this' was a reference to an instance of the DBContext class, which has a property 

public Set<School> Schools { get; set; }

then it would trigger 3 different database calls. But the result is different as this would be executed in the context of ALL schools, while my assumptions above only apply to a single school.

Upvotes: 0

r3bel
r3bel

Reputation: 1370

Public virtual properties are lazy loading in EF6. You can disable lazy loading for a DbContext, or use the .Include() method on the IQueryable to include the property in the first query.

http://www.entityframeworktutorial.net/EntityFramework4.3/lazy-loading-with-dbcontext.aspx

https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx

Once you "iterate" through the list (e.g. by calling the .Single(), .Count() or .ToArray() method), the query is executed and you have a in-memory list of your students. See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/query-execution for more details about query execution.

The first example would result in 3 queries, where the first returns the student with the given Id, the second returns the student count and the third returns the first 5 students ordered desc by their GPA property.

Regarding DDD => You can use some Layered architecture, with ApplicationServices and DomainServices, where DomainServices perform the domain logic and ApplicationServices load / transform the data.

The https://aspnetboilerplate.com/ template is for example a good starting point for domain driven design.

Upvotes: 0

Related Questions