Linq2DB未转换SELECT语句内计算表达式的问题求助

环境配置 #

使用的包版本 #

• Linq2DB.EntityFrameworkCore 7.3.0
• Linq2DB 5.1.0
• Microsoft.EntityFrameworkCore 7.0.4
• Npgsql 7.0.2
• Npgsql.EntityFrameworkCore.PostgreSQL 7.0.3

服务配置代码 #

builder.Services.AddDbContext<DtDbContext>(
    options =>
    {
        var dbOptions = new DbOptions();
        builder.Configuration.Bind(nameof(DbOptions), dbOptions);
        var connectionString = new NpgsqlConnectionStringBuilder
        {
            Username = dbOptions.User,
            Password = dbOptions.Secret,
            Host = dbOptions.Host,
            Database = dbOptions.Database,
        }.ToString(); 
        options
            .EnableDetailedErrors(true)
            .EnableSensitiveDataLogging(true)
            .UseNpgsql(connectionString)
            .UseLinqToDB(
                innerOptions =>
                {
                    innerOptions.AddCustomOptions(o => o
                        .UsePostgreSQL(connectionString, PostgreSQLVersion.v15)
                        .UseTraceLevel(TraceLevel.Verbose)
                        .UseTracing(static info => Console.WriteLine(info.SqlText)));
                });
    });

实体定义与查询需求 #

实体类 #

public class Entry
{
    public Guid Id { get; set; }
    public DateTime Ts { get; set; }
    public DateTime? Tt { get; set; }
    public string RecSrc { get; set; }
    public int Value { get; set; }
}

目标SQL查询 #

需要生成如下PostgreSQL查询，实现基于RecSrc的全连接，并通过复杂CASE逻辑合并数据：

SELECT
    COALESCE(srcBrn."Id", srcDef."Id") AS "Id",
    CASE
        WHEN srcDef."Ts" > COALESCE(srcBrn."Ts", '2000-01-01') THEN srcDef."Ts"
        WHEN srcBrn."Ts" >= COALESCE(srcDef."Ts", '2000-01-01') THEN srcBrn."Ts"
        ELSE '2000-01-01'
    END AS "Ts",
    CASE
        WHEN srcDef."Ts" > COALESCE(srcBrn."Ts", '2000-01-01') THEN srcDef."Tt"
        WHEN srcBrn."Ts" >= COALESCE(srcDef."Ts", '2000-01-01') THEN srcBrn."Tt"
        ELSE NULL
    END AS "Tt",
    CASE
        WHEN srcDef."Ts" > COALESCE(srcBrn."Ts", '2000-01-01') THEN srcDef."RecSrc"
        WHEN srcBrn."Ts" >= COALESCE(srcDef."Ts", '2000-01-01') THEN srcBrn."RecSrc"
        ELSE NULL
    END AS "RecSrc",
    CASE
        WHEN srcDef."Ts" > COALESCE(srcBrn."Ts", '2000-01-01') THEN srcDef."Value"
        WHEN srcBrn."Ts" >= COALESCE(srcDef."Ts", '2000-01-01') THEN srcBrn."Value"
        ELSE 0
    END AS "Value"
FROM (SELECT * FROM "Entry" WHERE "RecSrc" = 'default') srcDef
FULL JOIN (SELECT * FROM "Entry" WHERE "RecSrc" = 'branch') srcBrn
    ON srcBrn."Id" = srcDef."Id"

当前尝试的LINQ代码 #

var sourceDefault = _db.Set<Entry>()
    .Where(e => e.RecSrc == "default");
var sourceBranch = _db.Set<Entry>()
    .Where(e => e.RecSrc == "branch");

q = from srcDef in sourceDefault
    from srcBrn in sourceBranch.FullJoin(eBr => eBr.Id == srcDef.Id)
    select new Entry
    {
        Id = (Guid?) srcBrn.Id ?? srcDef.Id,
        Ts = srcDef.Ts > ((DateTime?) srcBrn.Ts ?? DateTime.MinValue)
            ? srcDef.Ts
            : srcBrn.Ts >= ((DateTime?) srcDef.Ts ?? DateTime.MinValue)
                ? srcBrn.Ts
                : DateTime.MinValue,
        Tt = srcDef.Ts > ((DateTime?) srcBrn.Ts ?? DateTime.MinValue)
            ? srcDef.Tt
            : srcBrn.Ts >= ((DateTime?) srcDef.Ts ?? DateTime.MinValue)
                ? srcBrn.Tt
                : null,
        RecSrc = srcDef.Ts > ((DateTime?) srcBrn.Ts ?? DateTime.MinValue)
            ? srcDef.RecSrc
            : srcBrn.Ts >= ((DateTime?) srcDef.Ts ?? DateTime.MinValue)
                ? srcBrn.RecSrc
                : null,
        Value = srcDef.Ts > ((DateTime?) srcBrn.Ts ?? DateTime.MinValue)
            ? srcDef.Value
            : srcBrn.Ts >= ((DateTime?) srcDef.Ts ?? DateTime.MinValue)
                ? srcBrn.Value
                : 0,
    };

遇到的问题 #

当前Linq2DB生成的SQL会直接拉取关联表的所有列，将SELECT中的复杂计算逻辑放到客户端执行，而非转换到SQL的SELECT子句中。但测试发现，当添加.OrderBy(e => e.Value)时，Linq2DB能正确将Value的计算表达式转换到ORDER BY子句，说明框架本身支持该表达式的SQL转换。尝试过.AsSubQuery()但无效果，不希望使用原生SQL，希望找到配置或代码写法让Linq2DB生成目标SQL。

解决方案 #

1. 使用Linq2DB原生查询语法替代EF Core Set #

Linq2DB对自身的查询语法支持更完善，将EF Core的Set<Entry>替换为Linq2DB的_db.GetTable<Entry>()，确保查询全程由Linq2DB解析：

var sourceDefault = _db.GetTable<Entry>()
    .Where(e => e.RecSrc == "default");
var sourceBranch = _db.GetTable<Entry>()
    .Where(e => e.RecSrc == "branch");

var q = from srcDef in sourceDefault
        fullJoin srcBrn in sourceBranch on srcDef.Id equals srcBrn.Id
        select new Entry
        {
            Id = srcBrn.Id ?? srcDef.Id,
            Ts = srcDef.Ts > (srcBrn.Ts ?? DateTime.MinValue)
                ? srcDef.Ts
                : (srcBrn.Ts >= (srcDef.Ts ?? DateTime.MinValue)
                    ? srcBrn.Ts
                    : DateTime.MinValue),
            Tt = srcDef.Ts > (srcBrn.Ts ?? DateTime.MinValue)
                ? srcDef.Tt
                : (srcBrn.Ts >= (srcDef.Ts ?? DateTime.MinValue)
                    ? srcBrn.Tt
                    : null),
            RecSrc = srcDef.Ts > (srcBrn.Ts ?? DateTime.MinValue)
                ? srcDef.RecSrc
                : (srcBrn.Ts >= (srcDef.Ts ?? DateTime.MinValue)
                    ? srcBrn.RecSrc
                    : null),
            Value = srcDef.Ts > (srcBrn.Ts ?? DateTime.MinValue)
                ? srcDef.Value
                : (srcBrn.Ts >= (srcDef.Ts ?? DateTime.MinValue)
                    ? srcBrn.Value
                    : 0),
        };

2. 提取重复计算逻辑为表达式（可选优化） #

将核心判断逻辑提取为可复用表达式，避免重复代码同时保证Linq2DB能正确解析：

Expression<Func<Entry, Entry, bool>> isDefaultNewer = (d, b) => 
    d.Ts > (b.Ts ?? DateTime.MinValue);
Expression<Func<Entry, Entry, bool>> isBranchNewer = (d, b) => 
    b.Ts >= (d.Ts ?? DateTime.MinValue);

var q = from srcDef in sourceDefault
        fullJoin srcBrn in sourceBranch on srcDef.Id equals srcBrn.Id
        select new Entry
        {
            Id = srcBrn.Id ?? srcDef.Id,
            Ts = isDefaultNewer.Invoke(srcDef, srcBrn) ? srcDef.Ts 
                : isBranchNewer.Invoke(srcDef, srcBrn) ? srcBrn.Ts 
                : DateTime.MinValue,
            Tt = isDefaultNewer.Invoke(srcDef, srcBrn) ? srcDef.Tt 
                : isBranchNewer.Invoke(srcDef, srcBrn) ? srcBrn.Tt 
                : null,
            RecSrc = isDefaultNewer.Invoke(srcDef, srcBrn) ? srcDef.RecSrc 
                : isBranchNewer.Invoke(srcDef, srcBrn) ? srcBrn.RecSrc 
                : null,
            Value = isDefaultNewer.Invoke(srcDef, srcBrn) ? srcDef.Value 
                : isBranchNewer.Invoke(srcDef, srcBrn) ? srcBrn.Value 
                : 0,
        };

3. 确认Linq2DB上下文配置有效性 #

检查EF Core配置中Linq2DB的自定义选项是否正确绑定到PostgreSQL，确保没有禁用表达式转换的配置，保证查询被Linq2DB完全接管而非EF Core原生处理。

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