Scalar function mapping - does not have default schema

[JonPSmith]

I am writing about scalar function mapping and I found that I had to define a schema of "dbo" for EF Core to find the user defined function (UDF). Is this because of the way I defined the UDF or doesn't the configuration of the HasDbFunction method pick up the default schema?

Also, the EF Core New inEF Core 2 documentation doesn't tell you that you need to use the HasDbFunction method to tell EF Core about the UDF, although @pmiddleton announcement comment does.

I get the following exception when I don't define the schema.

System.Data.SqlClient.SqlException : 'AverageVotesUdf' is not a recognized built-in function name.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at test.UnitTests.DataLayer.Ch08_ScalarFunctionMapping.TestUdfWorksOk() in C:\Users\Jon\Documents\Visual Studio 2017\Projects\EfCoreInAction\Test\UnitTests\DataLayer\Ch08_ScalarFunctionMapping.cs:line 63

Steps to reproduce

My unit test has a setup stage, which adds the UDF and seeds the database if a new database is created.

public class Ch08_ScalarFunctionMapping
{
    private readonly ITestOutputHelper _output;

    private readonly DbContextOptions<Chapter08EfCoreContext> _options;

    public Ch08_ScalarFunctionMapping(ITestOutputHelper output)
    {
        _output = output;

        var connection = this.GetUniqueDatabaseConnectionString();
        var optionsBuilder =
            new DbContextOptionsBuilder<Chapter08EfCoreContext>();

        optionsBuilder.UseSqlServer(connection);
        _options = optionsBuilder.Options;
        using (var context = new Chapter08EfCoreContext(_options))
        {
            if (context.Database.EnsureCreated())
            {
               //new database, so seed it with function and books
                context.AddUdfToDatabase();

                context.AddRange(EfTestData.CreateDummyBooks(setBookId: false));
                context.SaveChanges();
            }
        }
    }

    private class Dto
    {
        public int BookId { get; set; }
        public string Title { get; set; }
        public double? AveVotes { get; set; }
    }

    [Fact]
    public void TestUdfWorksOk()
    {
        //SETUP
        using (var context = new Chapter08EfCoreContext(_options))
        {
            var logIt = new LogDbContext(context);

            //ATTEMPT
            var bookAndVotes = context.Books.Select(x => new Dto
            {
                BookId = x.BookId,
                Title = x.Title,
                AveVotes = Chapter08EfCoreContext.AverageVotesUdf(x.BookId)
            }).ToList();

            //VERIFY
            foreach (var log in logIt.Logs)
            {
                _output.WriteLine(log);
            }
        }
    }
   //... other tests left out
}

My AddUdfToDatabase method looks like this:

    public static class AddUdfHelper
    {
        public const string UdfAverageVotes = nameof(Chapter08EfCoreContext.AverageVotesUdf);

        public static void AddUdfToDatabase(this DbContext context)
        {
            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    context.Database.ExecuteSqlCommand(
                        $"IF OBJECT_ID('dbo.{UdfAverageVotes}', N'FN') IS NOT NULL " +
                        $"DROP FUNCTION dbo.{UdfAverageVotes}");

                    context.Database.ExecuteSqlCommand(
                        $"CREATE FUNCTION {UdfAverageVotes} (@bookId int)" +
                        @"  RETURNS float
  AS
  BEGIN
  DECLARE @result AS float
  SELECT @result = AVG(CAST([NumStars] AS float)) FROM dbo.Review AS r
       WHERE @bookId = r.BookId
  RETURN @result
  END");
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    //I left this in because you normally would catch the expection and return an error.
                    throw;
                }
            }
        }
    }

My DbContext looks like this.
Note: I have commented out the HasSchema method - if that is applied then it works.

public class Chapter08EfCoreContext : DbContext
{
    public DbSet<Book> Books { get; set; }              
    public DbSet<Author> Authors { get; set; }          
    public DbSet<PriceOffer> PriceOffers { get; set; }  
    public DbSet<Order> Orders { get; set; }            

    public Chapter08EfCoreContext(                             
        DbContextOptions<Chapter08EfCoreContext> options)      
        : base(options) {}

    [DbFunction]
    public static double? AverageVotesUdf(int bookId)
    {
        throw new Exception();
    }


    protected override void
        OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new BookConfig());
        modelBuilder.ApplyConfiguration(new BookAuthorConfig());
        modelBuilder.ApplyConfiguration(new PriceOfferConfig()); 
        modelBuilder.ApplyConfiguration(new LineItemConfig());

        modelBuilder.HasDbFunction(
            () => AverageVotesUdf(default(int)));
        //.HasSchema("dbo");

    }

Further technical details

EF Core version: EF Core 2.0.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.3

[pmiddleton]

2.0 does not use the default schema. That was something which was missed. The functionality has been added to 2.1 See #9214

For now you will have to set the schema for each function.

Your function should be picked up with just the [DbFunction] as long as the method is declared on your context (which in your example it is). You can also define the schema using that attribute.

The only times you have to use HasDbFunction is if the function is declared on a class other than your dbContext, or you are using the manual translation callback.

Can you confirm that adding the schema to [DbFunction] and removing the HasDbFunction fixes your problem.

[JonPSmith]

Hi @pmiddleton,

Thanks for you your explanation. I tried adding the schema in [DbFunction], but I cannot just define the schema, i.e. [DbFunction(schema: "dbo")] produces a compilation error. I also tried [DbFunction(null, "dbo")] but that said the name should not be null.

I therefore think the Fluent API command in my original comment is the best solution, i.e.

        modelBuilder.HasDbFunction(() => AverageVotesUdf(default(int)));
           .HasSchema("dbo");

[JonPSmith]

Hi @pmiddleton,

Can I ask another question? I am trying to understand the HasTranslation Fluent API command. I can't see any documentation, but I did find the unit test UdfDbFunctionSqlServerTests and it looks like the HasTranslation method allowing your to define a system function as DbFunction - is that right??

[pmiddleton]

@JonPSmith - Try [DbFunction(Schema = "dbo")]

[pmiddleton]

HasTranslation is used for any situation where the default translation mechanism won't work for whatever reason and manual intervention is needed.

The main uses case is for system functions which have some quirks that need help working around.

Take a look at #9549 for an example of how HasTranslation was used to map DateDiff.

[JonPSmith]

Hi @pmiddleton,

Yep, [DbFunction(Schema = "dbo")] worked. Sorry I missed that.

Thanks for the link to #9549 - that is exactly what I needed. I'm not sure I will include that in the book, as it is pretty deep :).

PS. @pmiddleton - I would appreciate proof-reading my section in my book on scalar function mapping. I don't want to share my email here but if you go to my web site contact page and send me your email I can get a copy out to you.

[m1is]

Where can I find a full example of how to map a user defined function? All I get is a big fat exception when I try to run it. What's with the method throwing an exception? It doesn't look like a very intuitive pattern.

[pmiddleton]

Jon's example on this post works. What exception are you getting? What does your code look like?

[m1is]

My test UDF:
CREATE FUNCTION [dbo].[res_TestFunction] ( @p1 int ) RETURNS int AS BEGIN DECLARE @result int SELECT @result = @p1 RETURN @result END

In my data context:
[DbFunction(FunctionName = "res_TestFunction", Schema = "dbo")] public int TestFunction(int p1) { throw new Exception(); }

In OnModelCreating:
builder.HasDbFunction(() => TestFunction(default(int))).HasSchema("dbo");

[pmiddleton]

The method must be static. You also don't need to use both types of registration.

[m1is]

Okay so I changed the method to static:
[DbFunction(FunctionName = "res_TestFunction", Schema = "dbo")] public static int TestFunction(int p1) { throw new Exception(); }

Then I removed the registration in OnModelCreating but I still get an exception.

[pmiddleton]

You're going to need to post a repo example at this point. What exception are you getting?

[m1is]

I'll have to work on creating an example. The exception is the one in my method body of course. Why is the pattern to throw an exception? Obviously I don't know how it works underneath but on the surface the pattern doesn't make any sense.

[pmiddleton]

It sounds like you have a query which can't be fully translated and you are running into client side evaluation of the function. Have a look at this article for more help.

https://docs.microsoft.com/en-us/ef/core/querying/client-eval

[m1is]

Oh wow that's a cool new feature and now I get it. Turns out to test it I was calling the method directly and not in a LINQ query. Which leads me to my next question lets say I want to just call a scalar function is there anyway to wrap those and call them directly yet? Same thing with stored procedures, is there anyway to call those without using FromSql on a DbSet?

[pmiddleton]

Currently you can't call the UDFs directly. I'm currently working on that feature. See #9810

The same holds true for stored procs. IDK if there is a feature request for calling stored procs directly.