I'm using Postgresql with EF Core 3.1 on my ASP.NET project
I want to generate a sql fragment like this:
EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE f."MyDateTimeField")"
So, I have writed this code on OnModelCreating method of the DbContext:
modelBuilder
.HasDbFunction(typeof(MyDbFunctions).GetMethod(
nameof(MyDbFunctions.ExtractEpochFromTimestampWithTimezone)))
.HasTranslation(args
=> SqlFunctionExpression.Create(
"EXTRACT",
new []
{
new SqlFragmentExpression(
$"EPOCH FROM TIMESTAMP WITH TIME ZONE"),
args.First()
}, typeof(double), null));
But the generated SQL fragment was:
EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE, f."MyDateTimeField")
Note that SQL has one "," after word "ZONE".
How can I fix my code to generate the SQL fragment without this ","?
Any one that faced same issues as above. I have solve above issue with the following code
Test entity
EF Core Dbcontext with custom postgres EXTRACT function
EF function which is mapped
UPDATED CODE
The above method will fail when using nested functions. I have updated it with proper implementaion. I hope this will help others
Added a new class called ExtractExpression
Add below code in
OnModeCreating
function