Efcore: How to write DbFunction's translation?

[Kation]

When we use _ModelBuilder.HasDbFunction_ to add a database function to model
It generate the SQL with QUOTE char all of the arguments
With SQL Server 2016, there is a JSON_VALUE function
It's require first argument to be column without QUOTE, and second argument not be a parameter.

I wrote a test to solve first question but a bug exist.

Write a static method
```C#
public static class JsonExtensions
{
public static string JsonValue(string column, string path)
{
throw new NotSupportedException();
}
}

Write a expression translate method
```C#
public static class JsonExpressionTranslator
{
    public static Expression Translate(IReadOnlyCollection<Expression> expressions)
    {
        var items = expressions.ToArray();
        return new JsonExpression(items[0], items[1]);
    }
}

Write a json expression
```C#
public class JsonExpression : Expression
{
public JsonExpression(Expression column, Expression path)
{
Column = column;
Path = path;
}

public override ExpressionType NodeType => ExpressionType.Extension;

public Expression Column { get; private set; }

public Expression Path { get; private set; }

protected override Expression Accept(ExpressionVisitor visitor)
{
    if (visitor is ISqlExpressionVisitor specificVisitor)
    {
        string sql = $"JSON_VALUE([{Column.ToString().Trim('"')}],";
        specificVisitor.VisitSqlFragment(new SqlFragmentExpression(sql));
        visitor.Visit(Path);
        sql = ")";
        return specificVisitor.VisitSqlFragment(new SqlFragmentExpression(sql));
    }
    else
        return base.Accept(visitor);
}

protected override Expression VisitChildren(ExpressionVisitor visitor)
{
    return new JsonExpression(visitor.Visit(Column), visitor.Visit(Path));
}

public override Type Type => typeof(string);

public override string ToString() => $"JSON_VALUE([{Column.ToString().Trim('"')}], '{Path.ToString().Trim('"')}')";

}

Configure model
```C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod("JsonValue"), options => options.HasTranslation(JsonExpressionTranslator.Translate));
}

Write query
C# var path = "$.Filter"; //this argument used to be dynamic var result = dbContext.Items.Where(t => JsonExtensions.JsonValue("Values", path).ToArray();
It came out sql with
JSON_VALUE([Values],@__path_1)
And column name has no table relate, path is a parameter

-- How to generate sql without QUOTE?
-- How to generate sql that argument is not a PARAMETER?

[ajcvickers]

@smitpatel will provide some guidance here.

[ajcvickers]

@smitpatel Also, if this can be done in a way that we get docs out of it then all the better! /cc @divega

[smitpatel]

@Kation
You actually does not need so much of code to use JSON_VALUE function.
Based on documentation of function,

• 1st argument has to be string type. It can be any expression. You can pass literal string/parameter or use column. EF Core will take care of it automatically based on whatever value you pass in.
• 2nd argument has to be literal string at present. It cannot be parameter (this requirement is going away in SqlServer 2017). Since EF generates parameter for closure variable it is giving you 2nd error. So you just need to tell EF not to create parameter by using NotParameterized attribute on the parameter of your function.

So in your function definition, all you have to configure is its name & schema in database.
The overall code you need to add is as follows
```C#
public static class JsonExtensions
{
public static string JsonValue(string column, [NotParameterized] string path)
{
throw new NotSupportedException();
}
}

And in your OnModelCreating
```C#
modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
    .HasName("JSON_VALUE") // function name in server
    .HasSchema(""); // empty string since in built functions has no schema

Starting from 3.0, using followign in OnModelCreating
```C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Configure model
modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
.HasTranslation(e => SqlFunctionExpression.Create(
"JSON_VALUE", e, typeof(string), null));
}

With above code added, for query like where t.Log is column containing JSON
```C#
var path = "$.Filter"; //this argument used to be dynamic
var result = db.Blogs.Select(t => JsonExtensions.JsonValue(t.Log, path)).ToArray();

generates SQL which works correctly for me in SqlServer 2016

      SELECT JSON_VALUE([t].[Log], N'$.Filter')
      FROM [Blogs] AS [t]

Let me know if you still face issues with translation.

[cleftheris]

@smitpatel Just an addition to your comment above. In case someone uses a ValueConverter on the Blog mapping the Log property like so

builder.Property(x => x.Log).HasConversion(new ValueConverter<object, string>(
v => v != null ? JsonConvert.SerializeObject(v, serializerSettings) : null,
v => v != null ? JsonConvert.DeserializeObject<object>(v) : null));

then the on the Log property will not be of type String .

public class Blog
{
    public int BlogId { get; set; }

    public string Url { get; set; }

    public object Log { get; set; }
}

In this case the query will continue to translate to valid SQL if we cast the object back to string

var path = "$.Filter"; //this argument used to be dynamic
var result = db.Blogs.Select(t => JsonExtensions.JsonValue((string)t.Log, path)).ToArray();

By the way I tried this on a where clause and it still works!

[CoskunSunali]

For anyone looking around here in the future, be cautious using the JSON_VALUE function as it is a _scalar_ function and will play naughty when the value is longer than 4000 chars.

https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017

Extracts a scalar value from a JSON string.

Return Value
Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.

If the value is greater than 4000 characters:

• In lax mode, JSON_VALUE returns null.

• In strict mode, JSON_VALUE returns an error.

If you have to return scalar values greater than 4000 characters, use OPENJSON instead of JSON_VALUE.

[yechao59228866]

@Kation
You actually does not need so much of code to use JSON_VALUE function.
Based on documentation of function,

• 1st argument has to be string type. It can be any expression. You can pass literal string/parameter or use column. EF Core will take care of it automatically based on whatever value you pass in.
• 2nd argument has to be literal string at present. It cannot be parameter (this requirement is going away in SqlServer 2017). Since EF generates parameter for closure variable it is giving you 2nd error. So you just need to tell EF not to create parameter by using NotParameterized attribute on the parameter of your function.

So in your function definition, all you have to configure is its name & schema in database.
The overall code you need to add is as follows

public static class JsonExtensions
{
    public static string JsonValue(string column, [NotParameterized] string path)
    {
        throw new NotSupportedException();
    }
}

And in your OnModelCreating

modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
    .HasName("JSON_VALUE") // function name in server
    .HasSchema(""); // empty string since in built functions has no schema

With above code added, for query like where t.Log is column containing JSON

var path = "$.Filter"; //this argument used to be dynamic
var result = db.Blogs.Select(t => JsonExtensions.JsonValue(t.Log, path)).ToArray();

generates SQL which works correctly for me in SqlServer 2016

      SELECT JSON_VALUE([t].[Log], N'$.Filter')
      FROM [Blogs] AS [t]

Let me know if you still face issues with translation.

the program is dead after throw new NotSupportedException()

[wsaeed]

EF Core by default uses decimal(18, 2) for Convert.ToDecimal. I need to use different precision and scale for my conversion. For this, I have defined a DB function as below and also register it during model creation.

``` c#
[DbFunction("CONVERT", Schema = "")]
public static decimal Convert([NotParameterized]string dataType, double value)
{
throw new NotImplementedException();
}

I am calling the above function in my query.

``` c#
where DbFunctions.Convert("DECIMAL(31, 6)", sale.Discount/ 100) > 0

Here Amount is a double value (The database is designed 20 years back, so I can't change the datatype)

SQL generated is

WHERE CONVERT(N'DECIMAL(31, 6)', sale.Discount / 100)  > 0,

Note: The quotes are generated around DECIMAL, instead of a SQL literal.

Any ideas?

[yechao59228866]

public static class DbFunctions { [DbFunction("JSON_VALUE", "")] public static string JsonValue(string column, [NotParameterized] string path) { throw new NotSupportedException(); } }when program run to "throw new NotSupportedException()" then die

------------------ 原始邮件 ------------------
发件人: "Smoky"[e-mail];
发送时间: 2019年7月15日(星期一) 下午2:36
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Comment"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

EF Core by default uses decimal(18, 2) for Convert.ToDecimal. I need to use different precision and scale for my conversion. For this, I have defined a DB function as below and also register it during model creation.
[DbFunction("CONVERT", Schema = "")] public static decimal Convert([NotParameterized]string dataType, double value) { throw new NotImplementedException(); }

I am calling the above function in my query.
where DbFunctions.Convert("DECIMAL(31, 6)", sale.Discount/ 100) > 0

Here Amount is a double value (The database is designed 20 years back, so I can't change the datatype)

SQL generated is
WHERE CONVERT(N'DECIMAL(31, 6)', sale.Discount / 100) > 0,

Note: The quotes are generated around DECIMAL, instead of a SQL literal.

Any ideas?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[yechao59228866]

what's happened?

------------------ 原始邮件 ------------------
发件人: "Smoky"[e-mail];
发送时间: 2019年7月15日(星期一) 下午2:36
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Comment"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

EF Core by default uses decimal(18, 2) for Convert.ToDecimal. I need to use different precision and scale for my conversion. For this, I have defined a DB function as below and also register it during model creation.
[DbFunction("CONVERT", Schema = "")] public static decimal Convert([NotParameterized]string dataType, double value) { throw new NotImplementedException(); }

I am calling the above function in my query.
where DbFunctions.Convert("DECIMAL(31, 6)", sale.Discount/ 100) > 0

Here Amount is a double value (The database is designed 20 years back, so I can't change the datatype)

SQL generated is
WHERE CONVERT(N'DECIMAL(31, 6)', sale.Discount / 100) > 0,

Note: The quotes are generated around DECIMAL, instead of a SQL literal.

Any ideas?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[WeihanLi]

have you registered the DbFunction? @yechao59228866

register DbFunction like follows in DbContext:

    public class TestDbContext : DbContext
    {
        public TestDbContext(DbContextOptions<TestDbContext> options) : base(options)
        {
        }

        public DbSet<TestEntity> TestEntities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(() => DbFunctions.JsonValue(default, default));
        }
    }

you can have a look at my example

https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/samples/WeihanLi.EntityFramework.Samples/Program.cs

[ralmsdeveloper]

@WeihanLi , it is using decorator in its function, there is no need to map this using HasDbFunction, this is done internally.

What happens is that the string in EFCore is treated as UNICODE, but we can change this behavior.

@wsaeed , try this!
```csharp
public class SampleContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
builder.UseSqlServer("Server=.,1433;Database=SampleFunctions;Integrated Security=True;");
}

protected override void OnModelCreating(ModelBuilder builder)
{
    builder
        .HasDbFunction(typeof(SampleContext)
        .GetMethod(nameof(SampleContext.Convert)))
        .HasTranslation(args =>
        {
            var arguments = args.ToList();
            arguments[0] = new SqlFragmentExpression((string)((ConstantExpression)argumentos[0]).Value);
            return new SqlFunctionExpression(
                "CONVERT",
                typeof(decimal),
                arguments);
        });
}

public static decimal Convert([NotParameterized]string dataType, double value)
{
    throw new NotImplementedException();
}

}
```

[yechao59228866]

I use this demo,but also appear System.NotSupportedException:“Specified method is not supported.”

------------------ 原始邮件 ------------------
发件人: "liweihan"[e-mail];
发送时间: 2019年7月15日(星期一) 下午5:35
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Mention"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

have you registered the DbFunction? @yechao59228866 , you can have a look at my example

https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/samples/WeihanLi.EntityFramework.Samples/Program.cs

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[ralmsdeveloper]

@yechao59228866,
This works perfect for me!

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.Extensions.Logging;
using System;
using System.Linq;
using System.Linq.Expressions;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new SampleContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                db.Set<Issue11295>().Add(new Issue11295
                {
                    Description = "Sample",
                    Amount = 19.69d
                });
                db.SaveChanges();


                var data = db
                    .Issue11295
                    .Select(p => SampleContext.Convert("DECIMAL(31, 6)", p.Amount / 100));

                foreach (var item in data)
                {
                    Console.WriteLine($"Value: {item}");
                }

            }

            Console.ReadKey();
        }
    }

    public class SampleContext : DbContext
    {
        private static readonly ILoggerFactory _loggerFactory = new LoggerFactory()
                .AddConsole((s, l) => l == LogLevel.Information && s.EndsWith("Command"));

        public DbSet<Issue11295> Issue11295 { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            builder
                .UseLoggerFactory(_loggerFactory)
                .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ExtensionSample;Integrated Security=True;");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder
                .HasDbFunction(typeof(SampleContext)
                .GetMethod(nameof(SampleContext.Convert)))
                .HasTranslation(args =>
                {
                    var arguments = args.ToList();
                    arguments[0] = new SqlFragmentExpression((string)((ConstantExpression)arguments[0]).Value);
                    return new SqlFunctionExpression(
                        "CONVERT",
                        typeof(decimal),
                        arguments);
                });
        }

        public static decimal Convert(string dataType, double value)
            => throw new NotImplementedException();
    }

    public class Issue11295
    {
        public int Id { get; set; }
        public string Description { get; set; }
        public double Amount { get; set; }
    }
}

Output

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Issue11295] (
          [Id] int NOT NULL IDENTITY,
          [Description] nvarchar(max) NULL,
          [Amount] float NOT NULL,
          CONSTRAINT [PK_Issue11295] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (86ms) [Parameters=[@p0='?' (DbType = Double), @p1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Issue11295] ([Amount], [Description])
      VALUES (@p0, @p1);
      SELECT [Id]
      FROM [Issue11295]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT CONVERT(DECIMAL(31, 6), [p].[Amount] / 100E0)
      FROM [Issue11295] AS [p]
Value: 0.196900

[yechao59228866]

so our's vs setting is different？when the program run to the "throw new NotSupportedException()" ，then program can't running Continue ，i don't know what's happened

------------------ 原始邮件 ------------------
发件人: "Rafael Almeida"[e-mail];
发送时间: 2019年7月18日(星期四) 凌晨3:07
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Mention"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

@yechao59228866,
This works perfect for me!
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Query.Expressions; using Microsoft.Extensions.Logging; using System; using System.Linq; using System.Linq.Expressions; namespace Sample { class Program { static void Main(string[] args) { using (var db = new SampleContext()) { db.Database.EnsureDeleted(); db.Database.EnsureCreated(); db.Set().Add(new Issue11295 { Description = "Sample", Amount = 19.69d }); db.SaveChanges(); var data = db .Issue11295 .Select(p => SampleContext.Convert("DECIMAL(31, 6)", p.Amount / 100)); foreach (var item in data) { Console.WriteLine($"Value: {item}"); } } Console.ReadKey(); } } public class SampleContext : DbContext { private static readonly ILoggerFactory _loggerFactory = new LoggerFactory() .AddConsole((s, l) => l == LogLevel.Information && s.EndsWith("Command")); public DbSet Issue11295 { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder builder) { builder .UseLoggerFactory(_loggerFactory) .UseSqlServer("Server=(localdb)\mssqllocaldb;Database=ExtensionSample;Integrated Security=True;"); } protected override void OnModelCreating(ModelBuilder builder) { builder .HasDbFunction(typeof(SampleContext) .GetMethod(nameof(SampleContext.Convert))) .HasTranslation(args => { var arguments = args.ToList(); arguments[0] = new SqlFragmentExpression((string)((ConstantExpression)arguments[0]).Value); return new SqlFunctionExpression( "CONVERT", typeof(decimal), arguments); }); } public static decimal Convert(string dataType, double value) => throw new NotImplementedException(); } public class Issue11295 { public int Id { get; set; } public string Description { get; set; } public double Amount { get; set; } } }

Output
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Issue11295] ( [Id] int NOT NULL IDENTITY, [Description] nvarchar(max) NULL, [Amount] float NOT NULL, CONSTRAINT [PK_Issue11295] PRIMARY KEY ([Id]) ); info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (86ms) [Parameters=[@p0='?' (DbType = Double), @p1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; INSERT INTO [Issue11295] ([Amount], [Description]) VALUES (@p0, @p1); SELECT [Id] FROM [Issue11295] WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT CONVERT(DECIMAL(31, 6), [p].[Amount] / 100E0) FROM [Issue11295] AS [p] Value: 0.196900

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[ralmsdeveloper]

Post a repro project.
Maybe someone can help you, without this it is difficult to understand, what I posted is just a way to get around what you need for the moment.

[yechao59228866]

I see the problem ，because i'm not reg methods on the dbcontext
modelBuilder.HasDbFunction(() => MyDbFunctions.JsonValue(default(string), default(string)));

thank you!

------------------ 原始邮件 ------------------
发件人: "Rafael Almeida"[e-mail];
发送时间: 2019年7月18日(星期四) 上午9:36
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Mention"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

Post a repro project.
Maybe someone can help you, without this it is difficult to understand, what I posted is just a way to get around what you need for the moment.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[WeihanLi]

@ralmsdeveloper it seemed you had to register the DbFunction explicitly, it will not auto register (as my test, tested via Microsoft.EntityFrameworkCore.SqlServer 2.2.0 and 2.2.6). tested project here https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/samples/WeihanLi.EntityFramework.Samples/Program.cs
Supported auto register from 3.0?

[yechao59228866]

Microsoft.EntityFrameworkCore.SqlServer 2.2.4 in my program,It can't auto register.

------------------ 原始邮件 ------------------
发件人: "liweihan"[e-mail];
发送时间: 2019年7月18日(星期四) 上午10:37
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Mention"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

@ralmsdeveloper it seemed you had to register the DbFunction explicitly, it will not auto register (as my test, tested via Microsoft.EntityFrameworkCore.SqlServer 2.2.0 and 2.2.6). tested project here https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/samples/WeihanLi.EntityFramework.Samples/Program.cs
Supported auto register from 3.0?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[ralmsdeveloper]

@WeihanLi

I wanted to say that when using a method that has a [DbFunction] attribute there is no need to map in ModelBuild.

[DbFunction("CONVERT","")]
public static decimal Convert(string dataType, double value)
    => throw new NotImplementedException();

protected override void OnModelCreating(ModelBuilder builder)
{
    //This is no longer necessary.
    //builder.HasDbFunction(() => SampleContext.Convert(default, default));
}

[WeihanLi]

@ralmsdeveloper , which EFCore version you're using? I defined the dbFunction as follows:

[DbFunction("JSON_VALUE", "")]
public static string JsonValue(string column, [NotParameterized] string path)
{
    throw new NotSupportedException();
}

but I still had to register the DbFunction in the DbContext like follows:
(tested via Microsoft.EntityFrameworkCore.SqlServer 2.2.0 and 2.2.6), otherwise I'll get an System.NotSupportedException: Specified method is not supported exception

public class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options) : base(options)
    {
    }

    public DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(() => DbFunctions.JsonValue(default, default));
    }
}

[ralmsdeveloper]

Your method is outside of your DbContext, which is why you do not register automatically.

[WeihanLi]

@ralmsdeveloper thanks for your info, that's really a bad news, cause I wanna put the custom dbFunctions into a common library

[yechao59228866]

yes，my method is in other class ，so it's must register in modelBuilder
thank for you help

------------------ 原始邮件 ------------------
发件人: "Rafael Almeida"[e-mail];
发送时间: 2019年7月18日(星期四) 中午12:51
收件人: "aspnet/EntityFrameworkCore"[e-mail];
抄送: "59228866"[e-mail]; "Mention"[e-mail];
主题: Re: [aspnet/EntityFrameworkCore] How to write DbFunction'stranslation? (#11295)

Your method is outside of your DbContext, which is why you do not register automatically.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[salaros]

@ralmsdeveloper @yechao59228866 @WeihanLi this is not StackOverflow or Gitter, so please stop flooding this issue with your comments. @ajcvickers please remove all non-relevant comments, including mine, thank you.

[smitpatel]

Updated https://github.com/aspnet/EntityFrameworkCore/issues/11295#issuecomment-373852015 for 3.0 breaking changes.

[brunosantosrhsenso]

Does anyone knows how to generate CONVERT(VARCHAR, 10)?

I'm trying:

return SqlFunctionExpression.Create("CONVERT", new[] { new SqlConstantExpression(Expression.Constant("VARCHAR"), new StringTypeMapping("VARCHAR")), e.First() }, typeof(string), new StringTypeMapping("varchar"));

But it generates:

CONVERT('VARCHAR', 10);

[smitpatel]

C# return SqlFunctionExpression.Create("CONVERT", new[] { new SqlFragmentExpression("VARCHAR(max)"), e.First() }, typeof(string), new SqlServerStringTypeMapping());

[brunosantosrhsenso]

On 3.0 SqlFragmentExpression has no public constructor.

[roji]

@brunosantosrhsenso this has been fixed in 3.1 which is days away from being released. In the meantime you can try out preview3 which is available on nuget.org.

[elkami]

Your method is outside of your DbContext, which is why you do not register automatically.

thanks a lot, this comment help me a lot, because i was my prototype funcitonality on LinqPad, working like a charm, when i put that on my project, cannot translate DbFunction, and try the WeinhanLi example without success, i think something is missing there. where is JsonValue method?
now i going to research who can this things work on a unit test ef memory environment

[rogerfar]

I have been writing my JSON_VALUE like this, which I found is the only way that works:

            modelBuilder.HasDbFunction(typeof(DataContext).GetMethod(nameof(JsonValue)))
                        .HasTranslation(e => SqlFunctionExpression.Create("JSON_VALUE", e, typeof(String), null))
                        .HasParameter("column")
                        .HasStoreType("nvarchar(max)");

        public static String JsonValue(Object column, String path)
        {
            throw new NotSupportedException();
        }

But now SqlFunctionExpression.Create is obsolete I'm trying to rewrite it to new SqlFunctionExpression, but can't work out the parameters excactly.

[inexuscore]

@rogerfar Although your code works, SqlFunctionExpression.Create() has been marked as obsolete in .NET 5.0. It says use new SqlFunctionExpression() with appropriate arguments but I can't get it to work. The last constructor argument of type RelationalTypeMapping is confusing and I don't know what to do with it. Any ideas?

[inexuscore]

@Kation
You actually does not need so much of code to use JSON_VALUE function.
Based on documentation of function,

• 1st argument has to be string type. It can be any expression. You can pass literal string/parameter or use column. EF Core will take care of it automatically based on whatever value you pass in.
• 2nd argument has to be literal string at present. It cannot be parameter (this requirement is going away in SqlServer 2017). Since EF generates parameter for closure variable it is giving you 2nd error. So you just need to tell EF not to create parameter by using NotParameterized attribute on the parameter of your function.

So in your function definition, all you have to configure is its name & schema in database.
The overall code you need to add is as follows

public static class JsonExtensions
{
    public static string JsonValue(string column, [NotParameterized] string path)
    {
        throw new NotSupportedException();
    }
}

And in your OnModelCreating

modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
    .HasName("JSON_VALUE") // function name in server
    .HasSchema(""); // empty string since in built functions has no schema

Starting from 3.0, using followign in OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
            modelBuilder.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
                .HasTranslation(e => SqlFunctionExpression.Create(
                    "JSON_VALUE", e, typeof(string), null));
        }

With above code added, for query like where t.Log is column containing JSON

var path = "$.Filter"; //this argument used to be dynamic
var result = db.Blogs.Select(t => JsonExtensions.JsonValue(t.Log, path)).ToArray();

generates SQL which works correctly for me in SqlServer 2016

      SELECT JSON_VALUE([t].[Log], N'$.Filter')
      FROM [Blogs] AS [t]

Let me know if you still face issues with translation.

SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null) is deprecated. I've been struggling with this for hours and it feels dumb because I know it's a quick fix (LOL). SqlFunctionExpression.Create() is obsolete in .NET 5, any ideas on how to rewrite this and make VS stop complaining?

[smitpatel]

C# new SqlFunctionExpression( "JSON_VALUE", args, nullable: true, argumentsPropagateNullability: new [] { true, false }, typeof(string), null)

[rogerfar]

Thanks @smitpatel this works for me.

[inexuscore]

@smitpatel Ah! I see. It was argumentsPropagateNullability that I was missing. Excellent! Thank you very much, works.