Db functions literal mappings

[AndrewBoklashko]

Hi

In EF Core 2.0 we got a great new feature: support calling db functions.
But there is a problem: how do I have to map SQL literals to C# types?

For example, I can't write C# code that will be translated to something like this:

DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

because year is SQL literal.

I suppose it should be some wrapper around string, so that we can easily create instances of it as well as translate to SQL via query providers.

[smitpatel]

You would need to provide custom translation using HasTranslation API on DbFunctionBuilder. In that method you would implement translation using SqlFunctionExpression & SqlFragmentExpression.
new SqlFragmentExpression("year"); will generate year in the SQL as you want.
If you can share some code of your function and how are you registering it then I can provide translation.

[pmiddleton]

Sql Server actually can deal with the literal if it is in double quotes. In this case you can just register the method normally and pass in the datapart as a string.

[AndrewBoklashko]

Here is my query:

var ages = await db
    .Set<Child>()
    .Select(c => SqlFunctions.DateDiff("year", c.BirthDate.Value, DateTime.Now))
    .ToListAsync();

Db function:

public static int DateDiff(string diffType, DateTime startDate, DateTime endDate)
{
    throw new InvalidOperationException($"{nameof(DateDiff)} should be performed on database");
}

Registration in OnModelCreating:

builder.HasDbFunction(typeof(SqlFunctions)
    .GetMethod(nameof(SqlFunctions.DateDiff)));

This query throws the following exception:

System.Data.SqlClient.SqlException (0x80131904): Invalid parameter 1 specified for datediff.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__108_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Linq.AsyncEnumerable.<Aggregate_>d__6`3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

[smitpatel]

SqlServer can deal with double quotes but double quotes gets translated to N'year' so it throws.

            builder.HasDbFunction(typeof(SqlFunctions)
                .GetMethod(nameof(SqlFunctions.DateDiff)))
                .HasTranslation(args =>
                {
                    var newArgs = args.ToList();
                    newArgs[0] = new SqlFragmentExpression((string)((ConstantExpression)newArgs[0]).Value);
                    return new SqlFunctionExpression(
                        "DATEDIFF",
                        typeof(int),
                        newArgs);
                });

Above worked for me.

[AndrewBoklashko]

@smitpatel your solution works perfectly, thank you.

[pmiddleton]

@smitpatel - my bad. I forgot that escape was happening. Trying to multitask too much :)

[ggirard07]

@smitpatel sorry for dub question but where did you get the ref to SqlFunctions and SqlFunctions.DateDiff?

Using Microsoft.AspNetCore.All v2.0.9 right now and I can't resolve it. Is it because I am missing a NuGet dependency for SqlServer?

[smitpatel]

@ggirard07 - SqlFunctions.DateDiff is defined in user code as written in this post #10241 (comment)
The code I posted is just mapping user defined client side function to server