Entity Framework Core执行SQL聚合查询

尽管EF Core功能强大，但是仍然存在EF Core生成的SQL语句无法正确执行的情况。因此在少数场景下，仍然需要在EF Core中执行原生SQL语句。

1. EF Core无法正常执行的个例

1.1 数据表结构

Rains表存放降水数据，数据库为mysql，每小时一条记录，每5分钟更新一次数据。小时雨量存放在RF，逐5分钟雨量分别存放在RF1、RF2...RF12。RecTime记录整点时间（如2023-11-1 20:00:00），AMinute记录最后更新的分钟刻度（如5、10)。创建表的SQL语句如下：

create table Rains
(
    QuZhanHao int      not null,
    RecTime   datetime not null,
    AMinute   tinyint  not null,
    RF        smallint not null,
    RF1       smallint not null,
    RF2       smallint not null,
    RF3       smallint not null,
    RF4       smallint not null,
    RF5       smallint not null,
    RF6       smallint not null,
    RF7       smallint not null,
    RF8       smallint not null,
    RF9       smallint not null,
    RF10      smallint not null,
    RF11      smallint not null,
    RF12      smallint not null,
    primary key (QuZhanHao, RecTime)
);

1.2 EF Core实体类配置

Rain实体类

public class Rain
{
    public int QuZhanHao { get; set; }

    public DateTime RecTime { get; set; }

    public byte AMinute { get; set; }

    public short RF { get; set; }

    public short RF1 { get; set; }

    public short RF2 { get; set; }

    public short RF3 { get; set; }

    public short RF4 { get; set; }

    public short RF5 { get; set; }

    public short RF6 { get; set; }

    public short RF7 { get; set; }

    public short RF8 { get; set; }

    public short RF9 { get; set; }

    public short RF10 { get; set; }

    public short RF11 { get; set; }

    public short RF12 { get; set; }

    public Rain() { }
}

实体类与数据库映射，使用Fluent API进行配置

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
    {
    }

    public DbSet<Rain> Rains { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Rain>().ToTable("Rains");
        builder.Entity<Rain>().HasKey(r => new { r.QuZhanHao, r.RecTime });

        base.OnModelCreating(builder);
    }
}

1.3 查询需求

现在要获得Rains表最后更新的时间，于是用C#编写EF Core查询代码如下

//_context为依赖注入的ApplicationDbContext
_context.Rains.Max(x => x.RecTime.AddMinutes(x.AMinute))

结果出现如下错误

MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double) AS signed) minute))

EF Core生成的SQL语句如下，无法正确执行

SELECT MAX(DATE_ADD(`r`.`RecTime`, INTERVAL CAST(CAST(`r`.`AMinute` AS double) AS signed) minute))
      FROM `Rains` AS `r`

2. EF Core中执行原生SQL语句

为了解决这个问题，尝试让EF Core执行原生SQL语句。EF Core似乎不能直接将SQL聚集查询结果直接转为基本类，例如将Max(RecTime)的SQL查询结果转为C#的DateTime。这里先创建一个实体模型，然后进行映射配置，最后再从数据集（DbSet<LastUpdate> LastUpdates）中获得查询结果。步骤如下：

2.1 创建映射实体

用record类型的LastUpdate进行映射

    public record LastUpdate(DateTime LastRecTime);

2.2 在ApplicationDbContext中进行映射配置

 添加 DbSet<LastUpdate>，添加SQL查询映射，修改后的代码如下：

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
    {
    }

    public DbSet<Rain> Rains { get; set; }

    public DbSet<LastUpdate> LastUpdates { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Rain>().ToTable("Rains");
        builder.Entity<Rain>().HasKey(r => new { r.QuZhanHao, r.RecTime });

        builder.Entity<LastUpdate>().ToSqlQuery("select max(date_add(rectime,interval AMinute minute)) as LastRecTime from Rains");
        builder.Entity<LastUpdate>().HasNoKey();

        base.OnModelCreating(builder);
    }
}

2.3 获取聚合查询结果

 聚合查询的结果只有一个，因此从List<LastUpdate>中取第一个值，C#代码如下：

//_context为依赖注入的ApplicationDbContext
DateTime rainLastUpdate=_context.LastUpdates.First().LastRecTime;

经过这番操作后，终于得到正确的结果。检查EF Core生成的SQL代码，正是前面编写的原生SQL代码：

SELECT `v`.`LastRecTime`
FROM (
    select max(date_add(rectime,interval AMinute minute)) as LastRecTime from Rains
) AS `v`
LIMIT 1