如何将这些Linq查询结合起来，以及如何提高查询速度

Question

我有这个SQL脚本，我需要将其转换为Linq以便与EF Core一起使用：

SELECT  
A.*, B.*
FROM
[Orders] AS A
JOIN
(SELECT TOP 1 WITH TIES *
FROM [OrderMeta]
ORDER BY ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PostDate DESC)
) AS B ON (b.OrderId = A.OrderId)

以下是我将其转换为的内容：

public async Task<List<GetAllOrdersResult>> AdminGetOrderList()
{
using (var db = new SoulDrawContext())
{
var query = from A in db.Orders
join B in db.OrderMeta
on A.OrderId equals B.OrderId
select new GetAllOrdersResult
{
OrderId = A.OrderId,
UserId = A.UserId,
OrderDate = A.OrderDate.ToLocalTime().ToString("yyyy-MM-dd"),
Currency = A.Currency,
Amount = A.Amount,
PaymentMethod = A.PaymentMethod,
ShipTo = A.ShipTo,
Status = B.Status,
PostDate = B.PostDate.ToLocalTime().ToString("yyyy-MM-dd hh:mm"),
RefOrderId = A.RefOrderId
};
var query1 = from a in query
group a by a.OrderId
into g
orderby g.FirstOrDefault().PostDate
select new GetAllOrdersResult
{
OrderId = g.FirstOrDefault().OrderId,
UserId = g.FirstOrDefault().UserId,
OrderDate = g.FirstOrDefault().OrderDate,
Currency = g.FirstOrDefault().Currency,
Amount = g.FirstOrDefault().Amount,
PaymentMethod = g.FirstOrDefault().PaymentMethod,
ShipTo = g.FirstOrDefault().ShipTo,
Status = g.FirstOrDefault().Status,
PostDate = g.FirstOrDefault().PostDate,
RefOrderId = g.FirstOrDefault().RefOrderId
};
return await query1.ToListAsync();
}
}

问题：

在SSMS中运行的SQL脚本运行良好，查询大约需要32毫秒才能完成，但EF核心的linq查询大约需要9600毫秒。

order表中大约有2000条记录，ordermeta表中有60000条记录。

TODO

1. 如何正确地将SQL脚本转换为Linq？(或者组合2个linq查询？(

2. 如何提高查询速度？

Answer 1

试试这个，希望它能解决你的问题

public async Task<List<GetAllOrdersResult>> AdminGetOrderList()
{
using (var db = new SoulDrawContext())
{
var query = from A in db.Orders group A by A.OrderId into g 
join B in db.OrderMeta
on g.FirstOrDefault().OrderId equals B.OrderId
select new GetAllOrdersResult
{
OrderId = g.FirstOrDefault().OrderId,
UserId = g.FirstOrDefault().UserId,
OrderDate = g.FirstOrDefault().OrderDate.ToLocalTime().ToString("yyyy-MM-dd"),
Currency = g.FirstOrDefault().Currency,
Amount = g.FirstOrDefault().Amount,
PaymentMethod = g.FirstOrDefault().PaymentMethod,
ShipTo = g.FirstOrDefault().ShipTo,
Status = B.Status,//now your B in scope so you can use Directly
PostDate = B.PostDate.ToLocalTime().ToString("yyyy-MM-dd hh:mm"),
RefOrderId = g.FirstOrDefault().RefOrderId
};
query.OrderBy(B.PostDate);
return await query.ToListAsync();
}
}