C# 将含子查询的SQL转换为LINQ时出现MySQL语法错误如何解决

原有实现存在的问题 #

• 最大付款日期计算逻辑错误：原LINQ中计算dtpag时先取了全表vendas的最大付款时间，再匹配对应用户，和原始SQL中按每个用户单独算该用户最大付款时间的逻辑完全不符
• 类型转换写法非法：FirstOrDefault()返回的是vendas实体对象，直接用Convert.ToDateTime转换实体是无效操作，会导致EF Core生成非法的SQL语句，也就是你遇到的语法报错
• 表关联写法可优化：原写法用隐式交叉连接，可读性不如显式join写法

修正后的LINQ代码 #

var rel = await (
    from u in _contexto.usuarios
    join s in _contexto.libsys on u.cod equals s.codcli
    where _contexto.vendas.Any(v => v.coduser == u.cod)
        && _contexto.libsys.Any(l => l.codcli == u.cod && l.dataval >= DateTime.Now)
    orderby u.dtcad ascending
    select new RelatorioLicsModel
    {
        cod = u.cod,
        cpfcnpj = u.cpfcnpj,
        razaosocial = u.razaosocial,
        nome = u.nome,
        fone = u.fone,
        celular = u.celular,
        email = u.email,
        dtcad = u.dtcad,
        // 如果libsys的有效期字段实际叫pcpdataval，替换此处字段名即可
        validade = s.dataval.ToString(),
        // 按当前用户筛选后取最大付款日期，和原始SQL逻辑完全一致
        dtpag = _contexto.vendas.Where(v => v.coduser == u.cod).Max(v => (DateTime?)v.datapagamento)
    }
).ToListAsync();

补充说明 #

• 用(DateTime?)做可空转换是避免用户没有付款记录时Max函数抛出异常，你可以根据业务需求在后面加?? DateTime.MinValue指定默认值
• 如果你的MySQL版本比较低，不支持EF Core默认生成的窗口函数语法，可以升级MySQL Server版本，或者降级EF Core的MySQL驱动到和你当前数据库版本兼容的版本

内容的提问来源于stack exchange，提问作者MariINova