我使用的是Linq 2DB over EF Core数据库上下文，它连接到PostgreSQL 15，我想生成一个在SELECT语句中有复杂计算的查询。

环境

Package ：

• 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; }
}

要获取我的数据，我需要使用这样的查询：

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,
    };

问题

Linq 2DB翻译器生成的整个SQL看起来与我需要的相似。然而，生成的SQL的SELECT语句只包含来自两个连接表的所有列，而不是预期的表达式：

SELECT
    Coalesce(t1."Id", "srcDef"."Id"),
    "srcDef"."Ts",
    Coalesce(t1."Ts", :MinValue_1),
    t1."Ts",
    Coalesce("srcDef"."Ts", :MinValue_1),
    "srcDef"."Tt",
    t1."Tt",
    "srcDef"."RecSrc",
    t1."RecSrc",
    "srcDef"."Value_1",
    t1."Value_1"
FROM (
    SELECT
        e."Id",
        e."Ts",
        e."Tt",
        e."RecSrc",
        e."Value" as "Value_1"
    FROM
        "Entry" e
    WHERE
        e."RecSrc" = 'default'
) "srcDef"
FULL JOIN (
    SELECT
        e_1."Id",
        e_1."Ts",
        e_1."Tt",
        e_1."RecSrc",
        e_1."Value" as "Value_1"
    FROM
        "Entry" e_1
    WHERE
        e_1."RecSrc" = 'branch'
) t1 ON t1."Id" = "srcDef"."Id"

看起来Linq 2DB在客户端计算列值。
我们可以假设Linq 2DB无法将如此复杂的表达式转换为正确的SQL，但这并不正确，而且很容易检查。
让我们将.OrderBy(e => e.Value)添加到LINQ表达式中：
正如预期的那样，转换器将在生成的SQL的末尾添加ORDER BY语句，并成功转换Value列计算表达式：

ORDER BY
    CASE
        WHEN "srcDef"."Ts" > Coalesce(t1."Ts", :MinValue_1) THEN "srcDef"."Value_1"
        WHEN t1."Ts" >= Coalesce("srcDef"."Ts", :MinValue_1) THEN t1."Value_1"
        ELSE 0
    END;

因此，很明显，Linq 2DB翻译器可以翻译复杂的表达式，甚至可以在这里和那里使用它。
我尝试过使用.AsSubQuery()，但没有效果--它只是 Package 了上面的查询并保留了所有冗余列。
客户端计算意味着下载的数据量是原来的2倍。我不想使用原始SQL。

问题

有没有可能强制Linq 2DB生成SELECT语句，其中包含字段计算，如本文开头所示？有没有设置？我错过了什么？