Most of the examples I see on the internet show the navigation properties as either ICollection
or straight List
implementation. They are usually virtual
, to enable lazy-loading.
However, when you access such property, it will load the entire collection in memory and if you have a subquery after it (i.e. object.MyListProperty.Where(...)
) I have noticed that an SQL query will be issued for each item in the MyListProperty
.
How do I avoid this? I want the where
clause after the list property to execute on the SQL server, if possible. Can I use an IQueryable
navigation property? Is there any best-practice for such case?
My advice for best practise is to disable Lazy loading altogether. Instead force the caller to eagerly load navigation properties through include statements or by using projections.
In this way the records will only be loaded when they are explicitly requested.
When you want access to an
IQueryable
so you can defer the loading of the data, then make those queries against the DbContext instance and not from the object.Customer
has many thousands of transactions, so we don't want them to be eagerly or lazy loaded at all.It is good that you have identified that you want to use an
IQueryable<T>
we access them from theDbContext
directly, not from the instances that were previously retrieved.