Is there a way i can rewrite this query so it is not a correlated subqueries ?
var query = (from o in dbcontext.Orders
let lastStatus = o.OrderStatus.Where(x => x.OrderId == o.Id).OrderByDescending(x => x.CreatedDate).FirstOrDefault()
where lastStatus.OrderId != 1
select new { o.Name, lastStatus.Id }
).ToList();
This resulted in:
SELECT [o].[Name], (
SELECT TOP(1) [x0].[Id]
FROM [OrderStatus] AS [x0]
WHERE ([x0].[OrderId] = [o].[Id]) AND ([o].[Id] = [x0].[OrderId])
ORDER BY [x0].[CreatedDate] DESC
) AS [Id]
FROM [Orders] AS [o]
WHERE (
SELECT TOP(1) [x].[OrderId]
FROM [OrderStatus] AS [x]
WHERE ([x].[OrderId] = [o].[Id]) AND ([o].[Id] = [x].[OrderId])
ORDER BY [x].[CreatedDate] DESC
) <> 1
I have tried to do a join on a subquery but EF 2.1 is doing weird things... not what I expected;
var query = (from o in dbcontext.Orders
join lastStat in (from os in dbcontext.OrderStatus
orderby os.CreatedDate descending
select new { os }
) on o.Id equals lastStat.os.OrderId
where lastStat.os.StatusId != 1
select new { o.Name, lastStat.os.StatusId }).ToList();
In EF6 replacing
let x = (...).FirstOrDefault()
with
from x in (...).Take(1).DefaultIfEmpty()
usually generates better SQL.
So normally I would suggest
var query = (from o in db.Set<Order>()
from lastStatus in o.OrderStatus
.OrderByDescending(s => s.CreatedDate)
.Take(1)
where lastStatus.Id != 1
select new { o.Name, StatusId = lastStatus.Id }
).ToList();
(no need of DefaultIfEmpty
(left join) because the where
condition will turn it to inner join anyway).
Unfortunately currently (EF Core 2.1.4) there is a translation issue so the above leads to client evaluation.
The current workaround is to replace the navigation property accessor o.OrderStatus
with correlated subquery:
var query = (from o in db.Set<Order>()
from lastStatus in db.Set<OrderStatus>()
.Where(s => o.Id == s.OrderId)
.OrderByDescending(s => s.CreatedDate)
.Take(1)
where lastStatus.Id != 1
select new { o.Name, StatusId = lastStatus.Id }
).ToList();
which produces the following SQL for SqlServer database (lateral join):
SELECT [o].[Name], [t].[Id] AS [StatusId]
FROM [Orders] AS [o]
CROSS APPLY (
SELECT TOP(1) [s].*
FROM [OrderStatus] AS [s]
WHERE [s].[OrderId] = [o].[Id]
ORDER BY [s].[CreatedDate] DESC
) AS [t]
WHERE [t].[Id] <> 1