Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to write DbFunction's translation? #11295

Closed
Kation opened this issue Mar 16, 2018 · 39 comments
Closed

How to write DbFunction's translation? #11295

Kation opened this issue Mar 16, 2018 · 39 comments
Labels
closed-no-further-action The issue is closed and no further action is planned. customer-reported

Comments

@Kation
Copy link

Kation commented Mar 16, 2018

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

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

Write a expression translate method

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

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

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

Write query

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
Copy link
Member

@smitpatel will provide some guidance here.

@ajcvickers
Copy link
Member

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

@smitpatel
Copy link
Member

smitpatel commented Mar 16, 2018

@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.

@smitpatel smitpatel added closed-no-further-action The issue is closed and no further action is planned. and removed area-external labels Mar 16, 2018
@smitpatel smitpatel removed this from the 2.1.0 milestone Mar 16, 2018
@cleftheris
Copy link

cleftheris commented Jun 6, 2018

@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
Copy link

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
Copy link

@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
Copy link

wsaeed commented Jul 15, 2019

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?

@yechao59228866
Copy link

yechao59228866 commented Jul 15, 2019 via email

@yechao59228866
Copy link

yechao59228866 commented Jul 15, 2019 via email

@WeihanLi
Copy link
Contributor

WeihanLi commented Jul 15, 2019

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
Copy link
Contributor

@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!

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
Copy link

yechao59228866 commented Jul 17, 2019 via email

@ralmsdeveloper
Copy link
Contributor

@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
Copy link

yechao59228866 commented Jul 18, 2019 via email

@ralmsdeveloper
Copy link
Contributor

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
Copy link

yechao59228866 commented Jul 18, 2019 via email

@WeihanLi
Copy link
Contributor

@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
Copy link

yechao59228866 commented Jul 18, 2019 via email

@ralmsdeveloper
Copy link
Contributor

@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
Copy link
Contributor

@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
Copy link
Contributor

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

@WeihanLi
Copy link
Contributor

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

@yechao59228866
Copy link

yechao59228866 commented Jul 18, 2019 via email

@salaros
Copy link

salaros commented Jul 19, 2019

@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
Copy link
Member

Updated #11295 (comment) for 3.0 breaking changes.

@brunosantosrhsenso
Copy link

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
Copy link
Member

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

@brunosantosrhsenso
Copy link

On 3.0 SqlFragmentExpression has no public constructor.

@roji
Copy link
Member

roji commented Dec 2, 2019

@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
Copy link

elkami commented Mar 7, 2020

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
Copy link

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
Copy link

@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
Copy link

@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
Copy link
Member

smitpatel commented Dec 1, 2020

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

@rogerfar
Copy link

rogerfar commented Dec 1, 2020

Thanks @smitpatel this works for me.

@inexuscore
Copy link

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

@rogerfar
Copy link

@smitpatel I have tried your implementation as following:

            modelBuilder.HasDbFunction(typeof(DataContext).GetMethod(nameof(JsonValue)))
                        .HasTranslation(e => new SqlFunctionExpression("JSON_VALUE", e, true, new [] { true, false }, typeof(String), null))
                        .HasParameter("column")
                        .HasStoreType("nvarchar(max)");

Instead of

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

But it's not giving the same results.

When I run this query:

_dataContext.Tickets.Where(ticket => DataContext.JsonValue(ticket.MetaData, "$.approvedOn") == null)

I get this result at the first option:

SELECT * FROM [Tickets] AS [t] WHERE [t].[MetaData] IS NULL

While I get this with the 2nd option:

SELECT * FROM [Tickets] AS [t] WHERE JSON_VALUE([t].[MetaData], N'$.approvedOn') IS NULL

@smitpatel
Copy link
Member

@rogerfar - Updated my comment. A slightly incorrect argument passed optimizes SQL that way. Essentially if column is null then value of function is going to be null but other way is not true (if function value is null then column must have been null).

@inexuscore
Copy link

@smitpatel Ah I see, the argumentsPropagateNullability now has { false, false }. Gotta test it in production but thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
closed-no-further-action The issue is closed and no further action is planned. customer-reported
Projects
None yet
Development

No branches or pull requests