c# - Effective aggregation query using EF Core and PostgreSQL - Stack Overflow

admin管理员组

文章数量:1345166

I need to aggregate data using EF Core.

I have the following class:

public class Statistics 
{
    public long Orders { get; init; }
    public decimal Revenue { get; init; }
    public long Views { get; init; }
}

I use the following query:

var total = await context.Statistics
    .Where(...)
    .GroupBy(x => x.OrganizationId)
    .Select(x => new TotalStatistics
    {
        Orders = x.Sum(s => s.Orders),
        Revenue = x.Sum(s => s.Revenue),
        Views = x.Sum(s => s.Views),
    })
    .FirstAsync();

This code produces the following SQL:

SELECT 
    (SELECT COALESCE(sum(e0."Orders"), 0.0)::bigint
     FROM reporting."Statistics" AS e0
     WHERE NOT (e0."IsDeleted") AND e0."OrganizationId" = $1 
       AND e0."Type" = $2 
       AND e."OrganizationId" = e0."OrganizationId") AS "Orders", 
    (SELECT COALESCE(sum(e1."Revenue"), 0.0)
     FROM reporting."Statistics" AS e1
     WHERE NOT (e1."IsDeleted") 
       AND e1."OrganizationId" = $1 
       AND e1."Type" = $2 
       AND e."OrganizationId" = e1."OrganizationId") AS "Revenue",  
    (SELECT COALESCE(sum(e24."Views"), 0.0)::bigint
     FROM reporting."Statistics" AS e24
     WHERE NOT (e24."IsDeleted") 
       AND e24."OrganizationId" = $1 
       AND e24."Type" = $2 
       AND e."OrganizationId" = e24."OrganizationId") AS "Views"
FROM 
    reporting."Statistics" AS e
WHERE 
    NOT (e."IsDeleted") 
    AND e."OrganizationId" = $1 
    AND e."Type" = $2
GROUP BY 
    e."OrganizationId"
LIMIT 1

Which is pretty bad in terms of performance.

Is possible somehow to achieve the following SQL:

SELECT 
    SUM(e."Orders") AS "Orders",
    SUM(e."Revenue") AS "Revenue",  
    SUM(e."Views") AS "Views"
FROM 
    reporting."Statistics" AS e
WHERE 
    NOT (e."IsDeleted") 
    AND e."OrganizationId" = $1 
    AND e."Type" = $2
GROUP BY 
    e."OrganizationId"
LIMIT 1

And maybe someone knows why EF Core generates so bad SQL for this case?

本文标签： cEffective aggregation query using EF Core and PostgreSQLStack Overflow