How i can combine these Linq query and how improve query speed

c# entity-framework-core linq

Question

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

  1. How can I convert the SQL script to Linq correctly? (or combine 2 linq queries?)

  2. How can I improve query speed?

1
-1
7/13/2018 3:58:15 AM

Accepted Answer

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();
                    }
           }
-1
7/13/2018 8:44:00 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow