C#使用LINQ连接两集合分组取最大日期对应记录报错

问题描述 #

• C#项目中编写LINQ查询时持续触发表达式错误，预期可正常执行的业务逻辑无法运行
• 异常特征：将排序规则调整为按最大Id排序取首条结果时不会触发报错，但业务要求的分组日期排序逻辑执行失败
• 业务规则：按IDFiscal、CodigoXPCliente两个字段分组，取每组中DataRegistroUsuario日期值最大的对应记录，尝试多种写法均无法解决报错
• 问题代码如下：

var queryTemp =
            (from u in _userManager.Users.Where(u => u.TipoUsuario == "Cliente")
             join ass in context.AssessoresCliente on u.Id equals ass.ApplicationUser.Id into temp_ass
             from temp in temp_ass
             select new ListaBaseCliente_Atendimento
             {
                 Nome = u.Nome,
                 IDFiscal = u.IDFiscal,
                 Email = u.Email,
                 PhoneNumber = u.PhoneNumber,
                 DataPessoa = u.DataPessoa,
                 DataCadastro = temp.DataCadastro,
                 CodigoXPCliente = temp.CodigoXPCliente,
                 AssessorCambio = temp.AssessorCambio,
                 AssessorProtect = temp.AssessorProtect,
                 CodigoAssessorXP = temp.CodigoAssessorXP,
                 ClienteCambio = temp.ClienteCambio,
                 ClienteProtect = temp.ClienteProtect,
                 ClienteXP = temp.ClienteXP,
                 AssessorXP = temp.AssessorXP,
                 CadastroValido = true,
                 DataRegistroUsuario = temp.DataRegistroUsuario,
                 DataArquivo = DataHoje,
                 ContaXPNova = temp.ContaXPNova,
                 ImportacaoBitrix = temp.ImportacaoBitrix,
                 IdAssessoresCliente = temp.Id,
                 RegistroValido = temp.RegistroValido
             }).AsEnumerable(); 

    
    var agrup = from s in queryTemp group s by new { s.IDFiscal, s.CodigoXPCliente } into g
           select g.OrderByDescending(t => t.DataRegistroUsuario).FirstOrDefault();

故障根因 #

• 关联条件写法错误：JOIN时直接使用导航属性ass.ApplicationUser.Id作为关联键，而非实体的外键字段，会导致EF生成冗余的多层关联查询，多数数据库提供器在翻译这类关联+日期排序逻辑时会出现翻译失败，抛出表达式异常。而主键Id排序是数据库原生支持的简单排序逻辑，不会触发翻译错误。
• 空值未处理：左连接写法（into temp_ass from temp in temp_ass）在用户无匹配关联记录时，temp为null，直接访问temp的属性会抛出空引用异常。
• 可空类型排序问题：如果DataRegistroUsuario是可空类型DateTime?，未统一处理null值时，无论是数据库侧翻译还是内存排序都可能出现异常。
• AsEnumerable()放置位置不合理：在未完成分组筛选时就切换到内存执行，会拉取全量匹配数据到应用内存，既会提升内存占用，也会因为内存中实体的空值问题触发异常。

修复方案 #

优先选择数据库侧执行的写法，性能最优，不需要拉取全量数据：

注意：请将关联条件中的ass.ApplicationUserId替换为你实体中实际存储的外键字段名

var agrup = (from u in _userManager.Users.Where(u => u.TipoUsuario == "Cliente")
             join ass in context.AssessoresCliente on u.Id equals ass.ApplicationUserId into temp_ass
             from temp in temp_ass.DefaultIfEmpty()
             where temp != null
             group new { u, temp } by new { u.IDFiscal, temp.CodigoXPCliente } into g
             let maxRecord = g.OrderByDescending(x => x.temp.DataRegistroUsuario).First()
             select new ListaBaseCliente_Atendimento
             {
                 Nome = maxRecord.u.Nome,
                 IDFiscal = g.Key.IDFiscal,
                 Email = maxRecord.u.Email,
                 PhoneNumber = maxRecord.u.PhoneNumber,
                 DataPessoa = maxRecord.u.DataPessoa,
                 DataCadastro = maxRecord.temp.DataCadastro,
                 CodigoXPCliente = g.Key.CodigoXPCliente,
                 AssessorCambio = maxRecord.temp.AssessorCambio,
                 AssessorProtect = maxRecord.temp.AssessorProtect,
                 CodigoAssessorXP = maxRecord.temp.CodigoAssessorXP,
                 ClienteCambio = maxRecord.temp.ClienteCambio,
                 ClienteProtect = maxRecord.temp.ClienteProtect,
                 ClienteXP = maxRecord.temp.ClienteXP,
                 AssessorXP = maxRecord.temp.AssessorXP,
                 CadastroValido = true,
                 DataRegistroUsuario = maxRecord.temp.DataRegistroUsuario,
                 DataArquivo = DataHoje,
                 ContaXPNova = maxRecord.temp.ContaXPNova,
                 ImportacaoBitrix = maxRecord.temp.ImportacaoBitrix,
                 IdAssessoresCliente = maxRecord.temp.Id,
                 RegistroValido = maxRecord.temp.RegistroValido
             }).ToList();

如果需要保留内存执行逻辑，修改为以下写法即可：

var queryTemp =
        (from u in _userManager.Users.Where(u => u.TipoUsuario == "Cliente")
            join ass in context.AssessoresCliente on u.Id equals ass.ApplicationUserId into temp_ass
            from temp in temp_ass.DefaultIfEmpty()
            where temp != null
            select new ListaBaseCliente_Atendimento
            {
                Nome = u.Nome,
                IDFiscal = u.IDFiscal,
                Email = u.Email,
                PhoneNumber = u.PhoneNumber,
                DataPessoa = u.DataPessoa,
                DataCadastro = temp.DataCadastro,
                CodigoXPCliente = temp.CodigoXPCliente,
                AssessorCambio = temp.AssessorCambio,
                AssessorProtect = temp.AssessorProtect,
                CodigoAssessorXP = temp.CodigoAssessorXP,
                ClienteCambio = temp.ClienteCambio,
                ClienteProtect = temp.ClienteProtect,
                ClienteXP = temp.ClienteXP,
                AssessorXP = temp.AssessorXP,
                CadastroValido = true,
                // 可空日期类型请放开下面注释，给null值赋默认最小值
                // DataRegistroUsuario = temp.DataRegistroUsuario ?? DateTime.MinValue,
                DataRegistroUsuario = temp.DataRegistroUsuario,
                DataArquivo = DataHoje,
                ContaXPNova = temp.ContaXPNova,
                ImportacaoBitrix = temp.ImportacaoBitrix,
                IdAssessoresCliente = temp.Id,
                RegistroValido = temp.RegistroValido
            }).AsEnumerable(); 

var agrup = queryTemp
    .GroupBy(s => new { s.IDFiscal, s.CodigoXPCliente })
    .Select(g => g.OrderByDescending(t => t.DataRegistroUsuario).First())
    .ToList();

注意事项 #

• 编写EF关联查询时，必须直接使用实体的外键字段作为关联条件，不要通过导航属性的主键关联，否则EF会生成多余的JOIN逻辑，极易出现查询翻译失败。
• 左连接查询必须加空值判断，避免访问null对象属性抛出空引用异常。
• 非必要不要在查询中间调用AsEnumerable()/ToList()切换到内存执行，分组、排序、筛选逻辑尽量在数据库侧完成，数据量较大时性能差距可达百倍以上。

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