How can you chain group by using EF core 3-CodePudding

I want to generate a query using EF core 3.1 equivalent to this one:

SELECT g.Date, Count(*) countIntervals
FROM(
        SELECT
            TODATETIMEOFFSET(DATETIME2FROMPARTS(DATEPART(year, myTimestamp),1,1,0,0,0,0,0), ' 00:00') Date,
            DATEPART(month, myTimestamp) - (DATEPART(month, myTimestamp) % 3) interval
        GROUP BY 
            DATEPART(year, myTimestamp),
            DATEPART(month, myTimestamp) - (DATEPART(month, myTimestamp) % 3), 
            UserId
    ) as g
GROUP BY Date

here is my use case: "i want to count all intervals in each year"

I tried this in C# but i get an error System.InvalidOperationException: The LINQ expression could not be translated.:

var query = _context.DatesTable
    .GroupBy(m => new
    {
        Year = m.Timestamp.Year,
        interval = m.Timestamp.Month - m.Timestamp.Month % 3,
        UserId = m.UserId
    })
    .Select(g => new
    {
        Date = new DateTimeOffset(g.Key.Year, 1, 1, 0, 0, 0, TimeSpan.Zero),
        interval = g.Key.interval
    })
    .GroupBy(x => new { 
        Date = x.Date 
    })
    .Select(g => new
    {
        Date = g.Key.Date ,
        CountIntervals = g.Count()
    });
    
    query.ToList()

I already tried to load data in memory using AsEnumerable(). That works but it's not efficient:

var query = _context.DatesTable
    .GroupBy(m => new
    {
        Year = m.Timestamp.Year,
        interval = m.Timestamp.Month - m.Timestamp.Month % 3,
        UserId = m.UserId
    })
    .Select(g => new
    {
        Date = new DateTimeOffset(g.Key.Year, 1, 1, 0, 0, 0, TimeSpan.Zero),
        interval = g.Key.interval
    }).AsEnumerable();

    var result = query.GroupBy(x => new { 
        Date = x.Date 
    })
    .Select(g => new
    {
        Date  = g.Key.Date,
        CountIntervals = g.Count()
    });
    
    query.ToList()

Is there any efficient solution for this query ?

CodePudding user response：

Try the following query:

var query = _context.DatesTable
    .GroupBy(m => new
    {
        Year = m.Timestamp.Year,
        interval = m.Timestamp.Month - m.Timestamp.Month % 3,
        UserId = m.UserId
    })
    .Select(g => new
    {
        Year = g.Key.Year,
        interval = g.Key.interval
    })
    .GroupBy(x => new { 
        Year = x.Year 
    })
    .Select(g => new
    {
        Year = g.Key.Year,
        CountIntervals = g.Count()
    });
    
var result = query.ToList();

CodePudding user response：

A pretty direct transalation of your query goes like this.

var query = _context.DatesTable
    .GroupBy(m => new
    {
        Year = m.Timestamp.Year,
        interval = m.Timestamp.Month - m.Timestamp.Month % 3,
        UserId = m.UserId
    })
    .GroupBy(x => g.Key.Year)
    .Select(g => new
    {
        Year = g.Key,
        CountIntervals = g.Count()
    });
    
var result = await query.ToListAsync();