c# - LINQ or SQL: Group by with a sum of distinct values

Question

I have a database table, on SQL Server 2019, containing a time series of prices collected with multiple frequencies (daily, weekly or monthly) which I query using EF Core 3.1

I'm trying to extract these prices, aggregated by month, but without losing the information of the collection frequency.

From the following set of data:

I'm trying to get this one, which contains the aggregate average value of the prices, grouped by Month, and with the frequencies of the raw records.

These could be easily solved by using

string.Join(",",s.Select(innerSel=>innerSel.OriginalFrequency).Distinct())

but unfortunately, I can't use as I need to work on IQueriable objects and run the execution of the LINQ query only at the end when I take a subset of data, based on the page-size, because converting to a List the query before grouping means to get several thousands of records from the DB.

I was trying to use a combination of SUM and COUNT of the frequencies in order to easily understand which is the original combination by multiplication these two values (see the schema below) but the COUNT and SUM should count only distinct values, otherwise, it doesn't work.

Is there a way to not lose this information in some way, without overloading the database server requesting unnecessary data, or making multiple requests?

This is the code where I'm stuck:

var aggregatedMonthlyPrices = prices.GroupBy(g => new
    {
        g.DateMonth,
        g.DateYear
    }).Select(s => new
    {
        DateMonth = s.Key.DateMonth,
        DateYear = s.Key.DateYear
        Price=s.Average(avg=>avg.Price),
        FrequencySum= s.Sum(sum=>sum.DataCollectionFrequencyId),
        FrequencyCount = s.Count(),
    });

question from:https://stackoverflow.com/questions/66065994/linq-or-sql-group-by-with-a-sum-of-distinct-values

Answer 1

Waitting for answers