Question 208763 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – The number of groups and the total amount per request in EF Core

I have a some table in a PostgreSQL database:

id   internal_id  external_id  value
---  -----------  -----------  -----
1    1            null         5
2    1            null         3
3    null         2            8
4    null         2            3
5    3            null         2
6    null         4            9

I need to get the number of groups (internal_id, external_id) and the total sum of the values. This is how I do it with an SQL query and this is the result I get: count = 4, sum value = 30.

;with t as (
  select sum(value) val from phones
  group by internal_id, external_id
)
select count(*), sum(val) from t;

How can this SQL query be translated into Entity Framework Core to get data in one query? Now I get all the amounts of the groups and return them to the client, where I count the quantity and the total amount.

IList<decimal> result = await _Phones
  .Select(x => new { x.InternalId, x.ExternalId, x.Value })
  .GroupBy(x => new { x.InternalId, x.ExternalId })
  .Select(x => x.Sum(c => c.Value))
  .ToListAsync();

decimal? total = result.Sum();
int count = result.Count();

And I need to return only two result numbers from the database with one query. Without transferring an array of numbers over the network for subsequent calculation in memory. Is it possible?

Tags:

2

Answers


  1. 0

    Yes, you can get this in a single query. do like this. You need to GroupBy first with InternalId and ExternalId and then select the required details

    var query = context.Phones
                    .GroupBy(p => new { p.InternalId, p.ExternalId })
                    .Select(g => new { Count = g.Count(), Total = g.Sum(p => p.Value) });

var results = await query.ToListAsync();

    Edit:
    Based on your comment I think you are looking for the count of groups. so this should give you the required results.

    var results = context.Phones
                    .GroupBy(p => new { p.InternalId, p.ExternalId })
                    .Select(g => new { Count = g.Count(), Total = g.Sum(p => p.Value) });

int count = results.Count();
decimal total = results.Select(g => g.Total).Sum();

Console.WriteLine("Count: {0}, Total: {1}", count, total);
    Login or Signup to reply.
  2. 0

    Looks like you need to aggregate by the empty set (). You can do this by using GroupBy(x => new { })

    var results = _Phones
    .GroupBy(p => new { p.InternalId, p.ExternalId })
    .Select(g => g.Sum(p => p.Value))
    .GroupBy(g => new { })
    .Select(g2 => new {
    {
        Count = g2.Count(),
        Total = g2.Sum(g => g.Total),
    })
    .FirstAsync();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search