.NET Core中将含CTE的SQL语句转换为LINQ查询的方法

核心逻辑对齐说明 #

原SQL的逻辑可以拆为两个核心部分，不需要刻意模拟CTE语法结构，EF Core会自动生成等价的执行SQL：

1. 从公司表查询基础字段，计算公司成立到当前的总年份
2. 按成立年限分支计算报税日期：成立超过1年的，取公司最近一次报税日期加9个月；成立不满1年的，取成立日期加21个月

注意你原有代码用Inner Join关联报税表的写法不符合原SQL逻辑：内连接会过滤掉没有报税记录的公司，原SQL用关联子查询的方式会保留所有符合条件的公司，无报税记录时对应TaxDate返回null。

可直接运行的LINQ代码 #

首先确保引入EF Core的命名空间：

using Microsoft.EntityFrameworkCore;

完整查询代码如下，保留了你原有代码里的公司状态、用户名过滤条件：

var result = (
    from comp in this.AccountDB.TblCompanies
    where comp.CompanyStatus == true && comp.UserName == username
    // 计算成立年限，对应CTE中的TOTAL_YEARS字段
    let totalYears = EF.Functions.DateDiffYear(comp.IncorporatedDate, DateTime.Now)
    // 关联查询当前公司的最大报税日期，转可空类型避免无报税记录时抛错
    let maxTaxReturnDate = this.AccountDB.TblTaxes
        .Where(t => t.CompanyId == comp.CompanyId)
        .Max(t => (DateTime?)t.TaxReturnDate)
    select new CompanyTaxInfo
    {
        CompanyID = comp.CompanyId,
        CompanyName = comp.CompanyName,
        CompanyNumber = comp.CompanyNumber,
        IncorporatedDate = comp.IncorporatedDate,
        TotalYears = totalYears,
        // 对应SQL中CASE WHEN的分支计算逻辑
        TaxDate = totalYears > 1
            ? maxTaxReturnDate?.AddMonths(9)
            : comp.IncorporatedDate.AddMonths(21)
    }
).ToList();

关键实现说明 #

• 用let关键字做临时字段计算，逻辑上和SQL CTE的临时字段作用一致，EF Core生成SQL时会自动做优化，不会产生额外性能损耗
• 对报税日期做Max计算时显式转为DateTime?可空类型，避免成立超过1年但无报税记录的公司触发聚合函数异常
• 日期计算函数会被EF Core直接翻译为数据库端的DATEDIFF、DATEADD函数，不会拉取全量数据到内存计算，执行效率和原生SQL一致
• 如果你需要验证生成的SQL是否符合预期，可以在调用ToList()前加ToQueryString()方法输出最终执行的SQL语句做对比

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