I'm using Linq2DB over EF Core database context, which is connected to the PostgreSQL 15, and I want to generate a query that will have complex calculations inside the SELECT statement.
Environment
Packages:
- Linq2DB.EntityFrameworkCore 7.3.0
- Linq2DB 5.1.0
- Microsoft.EntityFrameworkCore 7.0.4
- Npgsql 7.0.2
- Npgsql.EntityFrameworkCore.PostgreSQL 7.0.3
A piece of configuration:
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)));
});
});
Intro
I have an entity defined like this:
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; }
}
To get my data, I need to use a query like this:
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"
To build such a query, I've made the following LINQ code:
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,
};
Problem
The entire SQL made by Linq2DB translator looks similar to what I need. However, the SELECT statement of a generated SQL just contains all columns from both joined tables rather than expressions as was expected:
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"
It looks like Linq2DB computes column values on client side.
We can suppose that Linq2DB just can't translate such a complex expression into a proper SQL. However, it is not true and it is easy to check.
Let's add .OrderBy(e => e.Value) to the LINQ expression:
As expected, translator will add the ORDER BY statement to the end of generated SQL, and with successfully translated Value column calculation expression:
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;
Therefore it is clear that Linq2DB translator can translate complex expressions and even use it here and there.
I've tried to use .AsSubQuery(), but no effect -- it just wraps the query above and keeps all redundant columns.
Client-side computation means that 2 times more data will be downloaded. I don't want to use raw SQL.
Question
Is there any chance to force Linq2DB to generate SELECT statement with fields computation like shown in the very beginning of this description? Any setting? What am I missed?
