We are having major performance issues with some of our LINQ-to-SQL queries.
We have a Case table, with a one-to-many relationship on CaseStatus (relationship table is CaseCaseStatus) Although, we want to keep track of all statusses on a case, only the last one really has any value.
We are building a query like follows:
IQueryable<Case> results = Repository.GetFullList(); // Returns a Queryable
if(filter.OnlyOpen){
results = results
.Where(c => c.CaseCaseStatusses.Any()
&& c.CaseCaseStatusses.OrderByDescending(cs => cs.Timestamp).FirstOrDefault().Status.IsClosed != true);
}
results.Select(x => Dto(x)).ToList();
Basicly we want to know if the last status, has a property "IsClosed == true".
When I monitor my database through the SQL profiler, I can see that the database is being queried when we hit the Select statement, not before. (As intented) But while the query is being executed, I can see a select query for each case:
SELECT [cs0].[Id], [cs0].[CaseId], [cs0].[Note], [cs0].[ShowOnClientPage], [cs0].[StatusId], [cs0].[TimestampUTC], [cs.Status0].[IsClosed]
FROM [CaseCaseStatusses] AS [cs0]
INNER JOIN [CaseStatusses] AS [cs.Status0] ON [cs0].[StatusId] = [cs.Status0].[Id]
WHERE [cs0].[CaseId] = <CaseId>
Since we have 8000+ records in our Case Table, it does 8000 of above select statements ... while we want 1 single query to be executed. Has this behavior changed in EFCore? I could swear I've done this before and it would output a single query (granted, it was a big unreadable query and not EFCore)
Is there another (better) way to perform these kind of queries?
Try this query, it should work with any EF version.