EF Core多条件列关联查询SQL转换失败的问题求助

问题背景 #

在Entity Framework Core中执行带多条件列的查询时，按照资料建议使用匿名对象关联列，运行时出现LINQ表达式无法转换为SQL的异常。拒绝使用客户端求值（避免加载整张表到内存），已尝试简化复现、类型转换、切换语法、手动构建表达式树、升级EF Core版本等方法，均未解决问题。

执行代码 #

var query = LendingDbContext
   .FinancialTransaction
   .Where(transaction => transaction.LoanApplicationId == loanApplicationId)
   .GroupJoin(
        LendingDbContext.TransactionCategory,
        transaction => new { Id = transaction.Id, Deleted = false },
        category => new { Id = category.FinancialTransactionId, Deleted = category.Deleted },
        (transaction, categoryList) => new TransactionWithCategory
        {
            FinancialTransaction = transaction,
            InclusionRule = transaction.InclusionRule,
            Categories = categoryList.Select(category => new CategoryModel
            {
                // 内容无关紧要
            })
        }
    );

报错信息 #

System.InvalidOperationException: 'The LINQ expression 'DbSet<TransactionCategory>()  
   .Where(t => !(object.Equals(  
        objA: new {   
           Id = (object)StructuralTypeShaperExpression(
               StructuralType: ACF.Data.Lending.Entities.FinancialTransaction
               ValueBufferExpression: ProjectionBindingExpression: Outer
               IsNullable: False).Id, 
           Deleted = False
        }, 
       objB: null)) && object.Equals(
       objA: new { 
           Id = (object)StructuralTypeShaperExpression(
               StructuralType: ACF.Data.Lending.Entities.FinancialTransaction
               ValueBufferExpression: ProjectionBindingExpression: Outer
               IsNullable: False).Id, 
           Deleted = False
        }, 
       objB: new { 
           Id = Convert.ToString((object)t.FinancialTransactionId), 
           Deleted = t.Deleted
        }))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

简化复现代码（同样报错） #

LendingDbContext.Collateral.Where(c => new Temp { Deleted = false } == new Temp { Deleted = c.Deleted })

目标生成SQL #

select {etc, columns}
from [FinancialTransaction] AS [f]
left join [InclusionRule] AS [i] on [f].[InclusionRule_Id] = [i].[Id]
left join TransactionCategory as category on category.FinancialTransaction_Id = f.Id and category.Deleted = false
where [f].[Deleted] = CAST(0 AS bit) and[f].[LoanApplicationId] = '{actual ID here}'
order by [f].[Id], [i].[Id]

解决方案 #

1. 拆分关联条件，提前过滤左连接表 #

EF Core对含常量值的匿名对象等值转换支持有限，可将Deleted=false的条件提前过滤左连接表，再用单一字段关联：

var query = LendingDbContext.FinancialTransaction
    .Where(ft => ft.LoanApplicationId == loanApplicationId)
    .GroupJoin(
        LendingDbContext.TransactionCategory.Where(tc => tc.Deleted == false),
        ft => ft.Id,
        tc => tc.FinancialTransactionId,
        (ft, tcGroup) => new TransactionWithCategory
        {
            FinancialTransaction = ft,
            InclusionRule = ft.InclusionRule,
            Categories = tcGroup.Select(tc => new CategoryModel
            {
                // 填充字段
            })
        }
    );

该写法与目标SQL逻辑完全一致，EF Core可正常转换为带多条件的LEFT JOIN。

2. 确保关联字段类型完全匹配 #

报错信息中出现Convert.ToString转换，说明transaction.Id与category.FinancialTransactionId类型不匹配。需显式统一类型：

// 假设transaction.Id是int，FinancialTransactionId是string
transaction => new { Id = transaction.Id.ToString(), Deleted = false },
category => new { Id = category.FinancialTransactionId, Deleted = category.Deleted }

匿名对象的属性类型必须完全一致，EF Core才能正确转换等值判断。

3. 使用导航属性（若实体已配置） #

如果FinancialTransaction与TransactionCategory已配置一对多导航属性，直接通过导航属性访问更简洁：

var query = LendingDbContext.FinancialTransaction
    .Where(ft => ft.LoanApplicationId == loanApplicationId)
    .Select(ft => new TransactionWithCategory
    {
        FinancialTransaction = ft,
        InclusionRule = ft.InclusionRule,
        Categories = ft.TransactionCategories.Where(tc => !tc.Deleted).Select(tc => new CategoryModel
        {
            // 填充字段
        })
    });

EF Core会自动生成包含category.Deleted=false条件的LEFT JOIN语句。

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