Make it easy to map built-in functions

[modevpro]

Exception message:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.JSON_QUERY", or the name is ambiguous.

Stack trace:
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot find either column "dbo" or the user-defined function or aggregate "dbo.JSON_QUERY", or the name is ambiguous.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Infrastructure.Services.FuneralHomeService.FindByServiceTypeAndRadius(Int32 serviceType, Int32 radius, Double latitude, Double longitude) in /Users/misha/Workspace/csharp_projects/Funeralocity/src/Infrastructure/Services/FuneralHomeService.cs:line 36
   at Web.Pages.Shared.Components.SearchResultsList.SearchResultsList.InvokeAsync(SearchFilterViewModel model) in /Users/misha/Workspace/csharp_projects/Funeralocity/src/Web/Pages/Shared/Components/SearchResultsList/SearchResultsList.cs:line 34
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeAsyncCore(ObjectMethodExecutor executor, ViewComponentContext context)
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeAsync(ViewComponentContext context)
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentHelper.InvokeCoreAsync(ViewComponentDescriptor descriptor, Object arguments)
   at Web.Pages.Search.Pages_Search_Index.ExecuteAsync() in /Users/misha/Workspace/csharp_projects/Funeralocity/src/Web/Pages/Search/Index.cshtml:line 26
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, Boolean invokeViewStarts)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Web.Middleware.CanonicalUrlMiddleware.Invoke(HttpContext context) in /Users/misha/Workspace/csharp_projects/Funeralocity/src/Web/Middleware/CanonicalUrlMiddleware.cs:line 21
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:3ab567cf-3e36-439f-9e60-931a7c82ac7b
Error Number:4121,State:1,Class:16

Steps to reproduce

1. Add JsonQuery method to DbContext

public class ApplicationDbContext : DbContext
{
        ...

        [DbFunction("JSON_QUERY", "")]
        public static string JsonQuery(string source, string path) => throw new NotSupportedException();
}

2. Call this method in a query

await _dbContext.FuneralHomes
                .Include(home => home.PriceList)
                .Where(ApplicationDbContext.JsonQuery(home.PriceList.ServiceBundlesJson, $"$.\"{serviceType}\"") != null)
                .ToListAsync();

Further technical details

EF Core version: 3.0.100-preview7-012821 and 3.0.100-preview8-013656 and also tried with a nightly build of preview 9
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: macOS 10.13.6 (High Sierra)
IDE: JetBrains Rider 2019.2

[pmiddleton]

The problem is not related to the mapping. The issue is the sql generation is sticking the dbo schema onto the function call. Since this is a builtin function there should not be a schema attached to the call.

You are correctly passing a blank schema to the DbFuction attribute. By chance are you also using the fluent api HasDbFunction and are passing in the dbo schema there, or not setting it to blank?

[smitpatel]

In 3.0, all DbFunction mappings are assumed to be UDFs. In case you are using it to map to built-in function, you need to use HasTranslation Fluent API on IDbFunctionBuilder.

[ajcvickers]

Note from triage: The experience here could be improved by:

• Adding a flag on the mapping attribute
• Adding fluent API to do this without falling down the HasTranslation
• Document how to do it currently - see Document built-in function mapping EntityFramework.Docs#1109

[smitpatel]

Removing milestone to discuss if we should do something for now or provide work-around to customer.
Current code would be

            modelBuilder
                .HasDbFunction(typeof(MyContext).GetMethod(nameof(MyContext.DatePart)))
                .HasTranslation(args => SqlFunctionExpression.Create("DatePart", args, typeof(int?), null));

[modevpro]

I did about the same

builder.HasDbFunction(typeof(ApplicationDbContext).GetMethod(nameof(JsonQuery)))
                .HasTranslation(args =>
                    SqlFunctionExpression.Create("JSON_QUERY", args, typeof(string),
                        new StringTypeMapping("nvarchar(max)")));

public static string JsonQuery(string source, string path) => throw new NotSupportedException();

but when I run a test that uses the InMemory database, it failed

System.NotSupportedException : Specified method is not supported.
   at Infrastructure.Data.ApplicationDbContext.JsonQuery(String source, String path)

[smitpatel]

That is because DbFunctions are relational concept. InMemory is not a relational database and it will try to execute your method which throws exception. Even if DbFunctions were core concept, how would InMemory database would know what is JSON_QUERY.

[modevpro]

I changed this method

public static string JsonQuery(string source, string path) => throw new NotSupportedException();

on

public static string JsonQuery(string source, string path) => null;

test is passed

Thanks @smitpatel