c# - 我如何结合这些 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 毫秒。

表中大约有 2,000 条记录，order表中有 60,000 条记录ordermeta。

去做

1. 如何正确地将 SQL 脚本转换为 Linq？（或结合 2 个 linq 查询？）

2. 如何提高查询速度？

标签： c#linqentity-framework-core

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();
                    }
           }