EF Core 2.0.2 generates multiple query for a subquery

[zulander1]

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 = db.TaskStatus.Where(x => x.TaskId == task.Id && 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

........

https://i.stack.imgur.com/22H8w.png

https://stackoverflow.com/questions/49834550/ef-core-2-0-2-generates-multiple-query-for-a-subquery

[zulander1]

I have also changed the query to:

let LastStatus = task.TaskStatus.Where(x => x.Deleted == false).OrderByDescending(x => x.CreatedDate).Select(x => x.Status.Name).First()

same results

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

[maumar]

Try using FirstOrDefault instead, EFCore is unable to mimic throwing behavior of First if it gets fully translated to sql, so it’s evaluated on the client. There are some issues with using “let” keyword, so you can also try without it - this should produce a single efficient query.

[zulander1]

It's working with the FirstOrDefault() what do you mean by “let” keyword, so you can also try without it

[maumar]

Defining “let LastStatus = ...” so you can reuse it later in the query. There are known problem when the sub query defined using let appears more than once later

[powermetal63]

Using the model from Many-to-many sample and 2.1.0-preview2-final, the following query still generates N + 1 queries though:

var query = db.Posts.Select(post => new
{
    Post = post,
    LastTag = post.PostTags.OrderByDescending(pt => pt.TagId).Select(pt => pt.Tag).FirstOrDefault(),
}).ToList();

[maumar]

@powermetal63 this is a known issue tracked by #10001

[powermetal63]

Well, it just looks the same as the one from original poster and they claim that their issue is fixed by removing the let operator and using FirstOrDefault instead of First.

Anyway, as I understand now, it's not supposed to be addressed in the initial 2.1. Good to know in case similar questions arrive at StackOverflow. Thanks.

[maumar]

@powermetal63 this one is different because the subquery returns more than one column. In SQL you can inline a subquery that returns a single scalar (like it was in the first example), but if the subquery returns an entity (i.e. multiple columns) it can't be done. For those cases we split it into two queries and patch the results on the client, but the optimization currently doesn't apply if there are any result operators in the subquery.

[powermetal63]

Makes sense. But...

var query = db.Tags.Select(tag => new
{
    Tag = tag,
    LastPost = tag.PostTags.OrderByDescending(pt => pt.PostId).Select(pt => pt.Post.Content).FirstOrDefault(),
})
.ToList();

still does N + 1 queries even if I select a single value. Just FYI :)

[smitpatel]

@powermetal63 - We are tracking it at #11186