CODEWITHSUNDEEP
mysqlsqlentity-framework
jay r bayog
jay r bayog

Reputation: 53

How to query in EF core with OrderByDescending, Take, Select and FirstOrDefault

So I've got a table named Summaries, it looks like this enter image description here

I need to get to sum the latest entries of TotalPieces based on CoveredDate and should be grouped by ServiceCode and queried by month for example, ServiceCode 'A' has entries on 2020-01-01, 2020-01-02, 2020-01-03, 2020-01-31, 2020-02-01, 2020-02-28, 2020-02-29 and ServiceCode 'B' has entries on 2020-01-01, 2020-01-02, 2020-01-31, 2020-02-20, 2020-02-21, i need to get the sum based on month, lastest entry on 'A' on January is on 2020-01-31, and 'B' has latest entry on 2020-01-31, I need to sum their 'TotalPieces', so I should get 25 + 25 = 50.

basically i need to do is

i got a working query, but this is just a workaround because i can't get it right on query.

            int sum_totalpieces = 0;
            foreach (var serviceCode in service_codes)
            {
                var totalpieces = _DbContext.ActiveSummaries.Where(acs =>
                                    acs.CoveredDate.Date.Month == query_month
                                    && acs.CoveredDate.Date.Year == query_year
                                    && acs.service_codes == serviceCode
                                    )
                    .OrderByDescending(obd => obd.CoveredDate)
                    .Take(1)
                    .Select(s => s.TotalPieces)
                    .ToList()
                    .FirstOrDefault();

                sum_totalpieces += totalpieces;
            }

the service_codes is just a List of string

If you guys could just get rid of the foreach block their and make it services_codes.Contains() on query, or another workaround to make the result faster that would be great. Thanks a lot.

Upvotes: 0

Views: 357

Answers (1)

Caius Jard
Caius Jard

Reputation: 74615

This will do it, but I don't think it will translate to SQL and run at the server:

    _DbContext.ActiveSummaries
            .Where(b =>
                b.CoveredDate >= new DateTime(2020,1,1) &&
                b.CoveredDate < new DateTime(2020,2,1) && 
                new [] { "A", "B" }.Contains(b.ServiceCode)
            )
            .GroupBy(g => g.ServiceCode)
            .Sum(g => g.OrderByDescending(gb=> gb.CoveredDate).First().TotalPieces);

If you want to do it as a raw SQL for best performance it would look like:

SELECT SUM(totalpieces)
FROM 
  x 
  INNER JOIN 
  (
    SELECT servicecode, MAX(covereddate) cd 
    FROM x 
    WHERE x.servicecode IN ('A','B') AND covereddate BETWEEN '2020-01-01' AND '2020-01-31'
  )y ON x.servicecode=y.servicecode and x.covereddate = y.cd

Upvotes: 1

Related Questions