I have a question about Entity Framework Core and using LINQ. I would like to get the other table details while accessing the Clients table. I can get them using below code. There are a total of around 10 tables I need to join, in this case is the below approach is good or any other, better approach? ClientId is the foreign key for all tables.
Actually I am getting a warning as below
[09:34:33 Warning] Microsoft.EntityFrameworkCore.Query Compiling a query which loads related collections for more than one collection navigation either via 'Include' or through projection but no 'QuerySplittingBehavior' has been configured. By default Entity Framework will use 'QuerySplittingBehavior.SingleQuery' which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'
Code:
var client = await _context.Clients
.Include(x => x.Address)
.Include(x => x.Properties)
.Include(x => x.ClientDetails)
-------------------
-------------------
-------------------
-------------------
.Where(x => x.Enabled == activeOnly && x.Id == Id).FirstOrDefaultAsync();
Actually when you use Eager loading (using include()) It uses left join (all needed queries in one query) to fetch data. Its default the ef behavior in ef 5.
You can set AsSplitQuery() in your query for split all includes in separated queries. like:
var client = await _context.Clients
.Include(x => x.Address)
.Include(x => x.Properties)
.Include(x => x.ClientDetails)
-------------------
-------------------
-------------------
-------------------
.Where(x =>x.Id == Id).AsSplitQuery().FirstOrDefaultAsync()
This approach needs more database connection, but it's nothing really important.
and for the final recommendation, I advise using AsNoTracking() for queries to high performance.
I have 3 different approaches depending on the version of EF Core you're using
EF Core 5 - as some have mentioned in previous answers there is new call which will simply break up the query into smaller subqueries and map all the relations in the end.
/*rest of the query here*/.AsSplitQuery();
If you are not able to just migrate your EF version you could still split the query manually
var client = await _context.Clients.FirstOrDefaultAsync(t => t.Enabled /*other conditions*/);
var Address = await _context.Addresses.FirstOrDefaultAsync(t => t.ClientId == client.Id);
/// Because they are tracked EF's entitytracker can under the hood
/// map the sub queries to their correct relations
/// in this case you should not use .AsNoTracking()
/// unless you would want to stitch relations together yourself
Another alternative is to write your query as a Select statement. This greatly improves performance but is a bit more of a hassle to construct.
var clientResult = await _context.Clients.Where(x => x.Id == id).Select(x => new
{
client = x,
x.Address,
Properties = x.Properties.Select(property => new
{
property.Name /*sub query for one to many related*/
}).ToList(),
x.ClientDetails
}).ToListAsync();
it doesn't take many includes to create cartesian explosion
you can read more up on the problem at hand in this article here
cartesian explosion in EF Core
and referral link to optimizing performance through EF Core can be found here Maximizing Entity Framework Core Query Performance