将指定SQL转换为EF Core 8查询时遇SQL转换警告求助

原始SQL语句 #

SELECT DISTINCT
    PRICE, UPDAT, f.ID AS fundid, f.coreserie
FROM 
    performance.FUND f
INNER JOIN
    performance.ULTIMO_FUND_NAV_LIMITED u ON u.ID = f.ID
WHERE 
    f.id = 51
    AND (f.startdate IS NULL OR u.updat >= f.startdate)
    AND (f.endDate IS NULL OR u.updat <= f.endDate)
ORDER BY 
    u.UPDAT;

问题场景 #

已完成Fund和UltimoFundNavLimited实体映射，编写的EF Core查询无法在数据库执行，收到警告：

Function is not convertible to SQL and must not be called in the database context

用户原查询代码：

var lst = await _context.Funds
                        .Where(f => f.Id == fundId)
                        .Include(x=>x.UltimoFundNavLimited)
                        .SelectMany(f => f.UltimoFundNavLimited, (f, u) => new { Fund = f, Ultimo = u })
                        .Where(x => x.Ultimo.Update >= x.Fund.StartDate  
                                    && x.Ultimo.Update <= x.Fund.EndDate)
                        .ToListAsync();

实体类定义 #

public class Fund
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CoreSerieId { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public List<UltimoFundNavLimited> UltimoFundNavLimited { get; set; }
}

public class UltimoFundNavLimited
{
    public int FundId { get; set; }
    public DateTime Update { get; set; }
    public decimal Price { get; set; }
    public decimal Value { get; set; }
}

解决方案 #

1. 确保关联关系配置正确 #

在DbContext的OnModelCreating中配置实体间的外键关联，保证EF Core能正确生成JOIN语句：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UltimoFundNavLimited>()
        .HasOne<Fund>()
        .WithMany(f => f.UltimoFundNavLimited)
        .HasForeignKey(u => u.FundId);
}

2. 修正查询代码 #

移除不必要的Include（避免客户端加载），补全NULL条件判断，添加Distinct和OrderBy匹配原始SQL逻辑，同时直接投影需要的字段减少数据传输：

var lst = await _context.Funds
    .Where(f => f.Id == fundId)
    .SelectMany(f => f.UltimoFundNavLimited, (fund, ultimo) => new
    {
        ultimo.Price,
        ultimo.Update,
        FundId = fund.Id,
        fund.CoreSerieId,
        fund.StartDate,
        fund.EndDate
    })
    .Where(x => (x.StartDate == null || x.Update >= x.StartDate) 
                && (x.EndDate == null || x.Update <= x.EndDate))
    .Select(x => new 
    {
        x.Price,
        x.Update,
        x.FundId,
        x.CoreSerieId
    })
    .Distinct()
    .OrderBy(x => x.Update)
    .ToListAsync();

问题原因分析 #

• Include与SelectMany混用会触发EF Core的客户端评估逻辑，导致无法转换为纯SQL执行，出现警告。
• 原查询未处理StartDate/EndDate为NULL的场景，与原始SQL逻辑不一致，同时可能引发客户端评估。
• 缺少Distinct和OrderBy，未完全匹配原始SQL的查询需求。

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