Can someone Turn this group by code into Pomelo Entity Framework?

Question

I'm trying to get the number of tasks performed on a daily basis and plot them into a chart, but the thing is I can't figure out the pomelo entity code.

 Select  FirstName,count(ToDoId)
    From todos as M 
    Inner join users as u
        on M.UserId=u.UserId
    where u.UserId=1
    GROUP BY M.UserId, CAST(M.CreatedAt AS DATE)

Answer 1

Assuming corresponding model classes for your two tables, that contain navigation properties, the LINQ query could look like this:

var todoCountByFirstNameAndDate = context.Todos.Include(e => e.User)
    .GroupBy(t => new {t.User.FirstName, t.CreatedAt.Date})
    .Select(g => new {g.Key.FirstName, g.Key.Date, TodoCount = g.Count()})
    .ToList();

The generated SQL would then be:

SELECT `u`.`FirstName`, CONVERT(`t`.`CreatedAt`, date) AS `Date`, COUNT(*) AS `TodoCount`
FROM `Todos` AS `t`
INNER JOIN `Users` AS `u` ON `t`.`UserId` = `u`.`UserId`
GROUP BY `u`.`FirstName`, CONVERT(`t`.`CreatedAt`, date)

Here is a complete and working console sample project, that demonstrates the bits and pieces:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace IssueConsoleTemplate
{
    //
    // Entities:
    //
    
    public class User
    {
        public int UserId { get; set; }
        public string FirstName { get; set; }
        
        public virtual ICollection<Todo> Todos { get; set; } = new HashSet<Todo>();
    }

    public class Todo
    {
        public int TodoId { get; set; }
        public DateTime CreatedAt { get; set; }
        public int UserId { get; set; }

        public User User { get; set; }
    }
    
    //
    // DbContext:
    //
    
    public class Context : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Todo> Todos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=So67149928",
                    b => b.ServerVersion("8.0.21-mysql")
                          .CharSetBehavior(CharSetBehavior.NeverAppend))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasData(
                    new User {UserId = 1, FirstName = "John"},
                    new User {UserId = 2, FirstName = "Jane"});
            
            modelBuilder.Entity<Todo>()
                .HasData(
                    new Todo {TodoId = 11, CreatedAt = new DateTime(2021, 4, 17, 14, 21, 41), UserId = 1},
                    new Todo {TodoId = 12, CreatedAt = new DateTime(2021, 4, 17, 18, 11, 21), UserId = 1},
                    new Todo {TodoId = 13, CreatedAt = new DateTime(2021, 4, 17, 14, 21, 41), UserId = 2},
                    new Todo {TodoId = 14, CreatedAt = new DateTime(2021, 4, 18, 18, 11, 21), UserId = 2});
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var todoCountByFirstNameAndDate = context.Todos.Include(e => e.User)
                .GroupBy(t => new {t.User.FirstName, t.CreatedAt.Date})
                .Select(g => new {g.Key.FirstName, g.Key.Date, TodoCount = g.Count()})
                .OrderBy(r => r.FirstName)
                    .ThenBy(r => r.Date)
                .ToList();

            Trace.Assert(todoCountByFirstNameAndDate.Count == 3);

            Trace.Assert(todoCountByFirstNameAndDate[0].FirstName == "Jane");
            Trace.Assert(todoCountByFirstNameAndDate[0].Date == new DateTime(2021, 4, 17));
            Trace.Assert(todoCountByFirstNameAndDate[0].TodoCount == 1);

            Trace.Assert(todoCountByFirstNameAndDate[1].FirstName == "Jane");
            Trace.Assert(todoCountByFirstNameAndDate[1].Date == new DateTime(2021, 4, 18));
            Trace.Assert(todoCountByFirstNameAndDate[1].TodoCount == 1);

            Trace.Assert(todoCountByFirstNameAndDate[2].FirstName == "John");
            Trace.Assert(todoCountByFirstNameAndDate[2].Date == new DateTime(2021, 4, 17));
            Trace.Assert(todoCountByFirstNameAndDate[2].TodoCount == 2);
        }
    }
}