I have this SQL script that I need to convert to Linq for use with 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)
Here is what I converted this to:
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();
}
}
Problem:
The SQL script running in SSMS is working fine, query takes about 32 ms to complete, but the linq query by EF core takes about 9600 ms.
There are about 2,000 records in order
table, and 60,000 records in ordermeta
table.
TODO
How can I convert the SQL script to Linq correctly? (or combine 2 linq queries?)
How can I improve query speed?
Try this one hope it solve your problem
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();
}
}