EF Core 2.0.2 generates multiple query for a subquery

c# entity-framework-core

Question

I am not sure if it's a bug not but I am getting some major performance issue. EF Core is generating multiple query for a simple statement.

This is the query:

        var query = (from task in db.Tasks
                     let LastStatus = task.TaskStatus.Where(x => x.Deleted == false).OrderByDescending(x => x.CreatedDate).Select(x => x.Status.Name).First()
                     select new
                     {
                         task.Id,
                         LastStatus
                     }).ToList();

Expected resuts:

SELECT [t0].[Id], (
    SELECT TOP (1) [t2].[Name]
    FROM [TaskStatus] AS [t1]
    INNER JOIN [Status] AS [t2] ON [t2].[Id] = [t1].[StatusId]
    WHERE ([t1].[TaskId] = [t0].[Id]) AND (NOT ([t1].[Deleted] = 1))
    ORDER BY [t1].[CreatedDate] DESC
    ) AS [LastStatus]
FROM [Tasks] AS [t0]

However EF is generating:

SELECT TOP(1) [x.Status0].[Name]
FROM [TaskStatus] AS [x0]
INNER JOIN [Status] AS [x.Status0] ON [x0].[StatusId] = [x.Status0].[Id]
WHERE ([x0].[TaskId] = @_outer_Id) AND ([x0].[Deleted] = 0)
ORDER BY [x0].[CreatedDate] DESC
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [x.Status0].[Name]
FROM [TaskStatus] AS [x0]
INNER JOIN [Status] AS [x.Status0] ON [x0].[StatusId] = [x.Status0].[Id]
WHERE ([x0].[TaskId] = @_outer_Id) AND ([x0].[Deleted] = 0)
ORDER BY [x0].[CreatedDate] DESC

enter image description here

UPDATE Tested with EF core 2.1.0-preview2-final still the same issue

1
2
4/14/2018 6:52:47 PM

Popular Answer

  1. Upgraded to EF 2.0.2 to EF core 2.1.0-preview2-final
  2. Used FirstOrDefault() instead of First(),
  3. Don't use "let" ("There are known problem when the sub query defined using let appears more than once later") See https://github.com/aspnet/EntityFrameworkCore/issues/11677 details
1
4/15/2018 2:16:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow