Object Relational Mappers (ORMs) are great tools for abstracting our database choice away and allowing us to get things done. As time goes on and our applications evolve, we realize the underlying database can get things done faster and more efficiently. Not just that, we will notice features that can help us deliver value to our end users in new and exciting ways. I’m here to tell you that we can continue to use LINQ and utilize database functions together.

In this post, we’ll see how we can configure Entity Framework Core (EF Core) to recognize existing database functions and also our custom database functions.

Prepackaged Functions

Entity Framework Core ships with many prepackaged functions under the SqlServerDbFunctionsExtensions static class. Some of the more useful functions in the class include Contains, Like, FreeText, and many date-based functions. These functions are specific to Microsoft SQL Server, which means the generated syntax in our SQL will be specific to SQL Server. Other database providers ship with their database-specific extensions.

Let’s take a look at how we could invoke these in our C# code.

var joSchmoes =
    await db.People
        .Where(p => EF.Functions.Like(p.Name, "jo%"))
        .Select(p => new
        {
            id = p.Id,
            name = p.Name,
            hasDate = EF.Functions.IsDate(p.Maybe)
        })
        .ToListAsync();

As you can see, we are invoking two database functions: Like and IsDate. When we run the LINQ query, we can see the generated SQL in the console output.

SELECT [p].[Id] AS [id], [p].[Name] AS [name], CAST(ISDATE([p].[Maybe]) AS bit) AS [hasDate]
FROM [People] AS [p]
WHERE [p].[Name] LIKE N'jo%'

Pretty cool! What about database functions not included with EF Core?

Adding More Database Functions

SQL Server has progressed since its inception and has support for JSON. These are powerful functions that can enable storage and retrieval mechanisms that rival many NoSQL offerings. Let’s enhance our previous query with the use of JSON_VALUE to retrieve a property value from a JSON column.

Let’s assume we store the following record in our database.

var john = new Person
{
    Maybe = DateTime.Now.ToShortDateString(),
    Name = "John Doe",
   // NOTE: IT IS IMPORTANT TO USE DOUBLE QUOTES!
    Json = @"{ ""hello"" : ""world"" }"
};

We should be able to retrieve the value of hello from our Json column.

var joSchmoes =
    await db.People
        .Where(p => EF.Functions.Like(p.Name, "jo%"))
        .Select(p => new
        {
            id = p.Id,
            name = p.Name,
            hasDate = EF.Functions.IsDate(p.Maybe),
            hello = Json.Value(p.Json, "$.hello")
        })
        .ToListAsync();

The call to JSON_VALUE won’t work immediately. We’ll need to modify our DbContext and tell it about this new function. The first step is to create a static class as a mechanism to call the database function.

public static class Json
{
    public static string Value(
        string expression,
        string path)
        => throw new InvalidOperationException($"{nameof(Value)}cannot be called client side");
}

We then need to register our method and its SQL translation with Entity Framework Core.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var jsonvalueMethodInfo = typeof(Json)
    .GetRuntimeMethod(
        nameof(Json.Value),
        new[] {typeof(string), typeof(string)}
    );

    builder
    .HasDbFunction(jsonvalueMethodInfo)
    .HasTranslation(args => 
        SqlFunctionExpression.Create("JSON_VALUE",
            args,
            typeof(string),
        null /* guess */
        )
);
}

There are few notable parts to this registration:

1. EF Core uses the MethodInfo to determine if the translation can continue.
2. GetRuntimeMethod is our friend.
3. We do not try to add an extension method of EF.DbFunctions, as that will add unnecessary overhead.
4. SqlFunctionExpression has a factory method. USE IT!
5. We don’t have to use all arguments, but in this case, we do.
6. EF Core functions do not support generics.

When we execute our LINQ statement, we can see the SQL generated correctly.

SELECT [p].[Id] AS [id], [p].[Name] AS [name], CAST(ISDATE([p].[Maybe]) AS bit) AS [hasDate], JSON_VALUE([p].[Json], N'$.hello') AS [hello]
FROM [People] AS [p]
WHERE [p].[Name] LIKE N'jo%'

Not only that, but we have the result in our projected model.

Custom Functions

So far, we’ve called prepackaged database functions and Microsoft SQL Server functions, but what our database functions? As you guessed, it is similar to the above example with a few minor caveats. Let’s start by creating a custom function.

IF OBJECT_ID('dbo.FortyTwo') IS NOT NULL
    DROP FUNCTION FortyTwo
GO
Create FUNCTION FortyTwo() RETURNS int AS
BEGIN
    return 42;
END

The function FortyTwo will return the number 42 when we invoke it. We need to follow the same steps from above, starting with a static class.

public static class AnswersToTheUniverse
{
    public static int What() 
        => throw new InvalidOperationException($"{nameof(What)}cannot be called client side");
}

We then follow it up with registration in our DbContext’s OnModelCreating method.

var methodInfo =
    typeof(AnswersToTheUniverse)
        .GetRuntimeMethod(nameof(AnswersToTheUniverse.What), new Type[0]);

builder
    .HasDbFunction(methodInfo)
    .HasTranslation(args =>
        SqlFunctionExpression.Create(
            builder.Model.GetDefaultSchema(), 
            "FortyTwo",
            args,
            typeof(int),
            null /* guess */
        ));

Pay close attention. We need to pass in the schema to our SqlFunctionExpression. Without the schema, this call will fail. We can get the schema by calling builder.Model.GetDefaultSchema, but it is important that we make this call after setting the final schema for our context.

Finally, we can call our custom function in our LINQ expression.

var joSchmoes =
await db.People
    .Where(p => EF.Functions.Like(p.Name, "jo%"))
    .Select(p => new
    {
        id = p.Id,
        name = p.Name,
        hasDate = EF.Functions.IsDate(p.Maybe),
        hello = Json.Value(p.Json, "$.hello"),
        meaningOfLife = AnswersToTheUniverse.What()
    })
    .ToListAsync();

We can see the SQL translation performs correctly.

SELECT [p].[Id] AS [id], [p].[Name] AS [name], CAST(ISDATE([p].[Maybe]) AS bit) AS [hasDate], JSON_VALUE([p].[Json], N'$.hello') AS [hello], [dbo].[FortyTwo]() AS [meaningOfLife]
FROM [People] AS [p]
WHERE [p].[Name] LIKE N'jo%'

Even better, we can see the results in our debugger window.

Conclusion

We usually start using ORMs because it can accelerate our rate of development. They can indeed help us accomplish more in a shorter period, but they also have their drawbacks. As our codebase matures, we begin to focus more of our efforts on optimization. Using database functions, especially those dealing with JSON can be great for our users. Luckily for us developers, the EF Core team has exposed many helper classes to make adding new functionality to our applications a breeze.

If you want to run this sample, I have published the entire project on GitHub.

Let me know what functions you’re looking to add in the comments below.