Entity Framework & Lazy Loading

[P00HB33R]

Does anyone know why, by default, entity framework loads all related enitites when querying a table?
I have tried manually enabling lazyloading, but when I run SQL Profiler while executing the query i can still see a Stored Procedure being executed for each related entity table.

This severly impacts the performance & amount of db calls made. I have resorted to creating anonymous objects for each call to limit the records to only the specific ones I want.

Comparison: Normal query on a Customer in my table returns the customer, with all invoices, all quotes, all contacts, etc.
I just need the main info related to the customer eg. Name, Address etc.

My point is, is it not very inefficient to always call all related entities? And would that cripple your SQL server when you have 100+ users working on it?

Had to redo all my queries in entity framework to use the select clause, and lo and behold speed/response increases are through the roof.

 

[Spacerat]

Yep your navigation properties should always be lazy loaded. Otherwise, think of when you return a whole list of objects, each with all their navigation properties already loaded. Hell that will kill your apps performance for even 1 user.

But cant help you sorry. I don't use EF, not a fan. I prefer my domain objects 'pure'

 

[Raithlin]

I find using stored procs in EF works best.

 

[P00HB33R]

My main concern is, even if I enable lazy loading manually, it still queries all related entities. I have also disabled the auto proxy creation option.

 

[P00HB33R]

Yep your navigation properties should always be lazy loaded. Otherwise, think of when you return a whole list of objects, each with all their navigation properties already loaded. Hell that will kill your apps performance for even 1 user.

But cant help you sorry. I don't use EF, not a fan. I prefer my domain objects 'pure'

Could you maybe elaborate on 'pure'?

 

[_kabal_]

well it depends on each situation.

if you have an object that has many one to one relationships on it, then EAGER loading is more performant than LAZY loading (assuming your indexes are correct, and you are loading single/limited sets)

This is because this data can be loaded in a single query.


For an invoice, you most likely want the lines too. This can also usually be loaded in a single query. (This however obviously can become a problem when you run "repository.Get<Invoice>" and you now load the entire database of invoices and it's lines)



Usually where these massive data-sets come from is a "flawed" design.

eg.
User ---has ---> Audit Events
Audit Event ---is owned by--->User

If a user can have million of audit events, this can be problematic.
Now you need to mark AuditEvents as LAZY, but in this case I would argue that Customer.AuditEvents is not a valid navigation, and you should rather explicitly load AuditEvents for a specific customer ID.


The problem I have with LAZY loading is that it is not explicit in what is happening. Is it LAZY? is it EAGER? you have no idea unless you go look at the definition



In general I now avoid ORM where possible. It is great for getting up and running quickly, but more difficult to fix individual performance issues.

 

[Spacerat]

This is bound to open a whole architecture discussion.

Could you maybe elaborate on 'pure'?

With 'pure' I mean the Domain Objects should be as independent as possible of any kind of framework like EF. EF is supposed to be an ORM. It's job is to hydrate your DO and nothing else. If your DO is dependent on the ORM or ORM base classes, then your architecture is wrong. I.e. my preference is that the DO should be as much pure business logic as possible, because that is where your business rules and domain behaviour live.

well it depends on each situation.

For an invoice, you most likely want the lines too. This can also usually be loaded in a single query. (This however obviously can become a problem when you run "repository.Get<Invoice>" and you now load the entire database of invoices and it's lines)

In general I now avoid ORM where possible. It is great for getting up and running quickly, but more difficult to fix individual performance issues.

Some ppl prefer the data layer/repository to only return DTOs. Then you hydrate the Domain Object from the DTO.

Other ppl prefer the data layer to return already-hydrated domain objects.

Either way whether you return a list of domain objects or a single one, my humble opinion is that for the sake of performance all navigational properties should be lazy loaded. This also boils down to how you choose your Aggregate Roots. To my mind, an Invoice is an Aggregate Root. With Lines as a lazy-loaded collection. In my case I have a State with state tracking against the invoice as well. So the StateId is a field on the Invoice, but the State navigational property and its audit trail should be lazy-loaded.

 

[Solarion]

I have a quick EF question. In my model class, say Vehicles for example, do I have to call my primary key Id or can I keep it as VehicleID?

The VehicleID is the Identity field in SQL.

 

[XennoX]

I have a quick EF question. In my model class, say Vehicles for example, do I have to call my primary key Id or can I keep it as VehicleID?

Your choice. I prefer the latter, as it is more explicit about which id is being referred to.

The VehicleID is the Identity field in SQL.

This is fine. The only time it may cause some confusion is if you've used the Fluent API to change the name of the column in your table to be different to the name of the property in your class - but that shouldn't affect the way you interact with the C# code, only the SQL.

 

[Solarion]

Thanks Xennox. I think I misinterpreted what this guy was saying in an EF video. Thanks!