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?
A navigation property is an optional property on an entity type that allows for navigation from one end of an association to the other end. Unlike other properties, navigation properties do not carry data.
Basically a scalar property is mapped to a column (int, string, ...) A navigation property is mapped to a relation. e.g Order. OrderDetails brings you to all ORderDetails of a specific order.
Navigation properties are typically defined as virtual so that they can take advantage of certain Entity Framework functionality such as lazy loading.
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.
There are 3rd party products that support include with filters, as described in this post: How to filter include entities in entity framework, but in my experience this further complicates down-stream processing of the objects that are retrieved. If the entity object is loaded outside of method
X
, because methodX
can't know for sure if the navigation properties have been loaded with the correct filters, methodX
starts off by re-querying for the precise rows that it knows it needs.
using (var context = new MyDbContext())
{
context.Configuration.LazyLoadingEnabled = false;
// TODO: load your data
...
}
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.using (var context = new MyDbContext())
{
context.Configuration.LazyLoadingEnabled = false;
var customer = context.Customers.First(x => x.Id == 123);
...
// count the transactions in the last 30 days for this customer
int customerId = customer.Id;
DateTime dateFrom = DateTime.Today.AddDays(-30)
// different variations on the same query
int transactionCount1 = context.Customers.Where(x => x.Id == customerId)
.SelectMany(x => x.Transactions.Where(x => x.TransactionDate >= dateFrom))
.Count();
int transactionCount2 = context.Customers.Where(x => x.Id == customerId)
.SelectMany(x => x.Transactions)
.Where(x => x.TransactionDate >= dateFrom)
.Count();
int transactionCount3 = context.Transactions.Where(x => x.CustomerId == customerId)
.Where(x => x.TransactionDate >= dateFrom)
.Count();
}
It is good that you have identified that you want to use an IQueryable<T>
we access them from the DbContext
directly, not from the instances that were previously retrieved.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With