Entity Framework Core Pitfalls: Calling DB Functions in LINQ Queries as Extension Methods

Introduction

Another one for my series on Entity Framework Core Pitfalls. Sometimes, even when using EF Core, we need to perform some operations, like calling a database (DB) function, that is not mapped to LINQ. We've been able to do this for a long time, and it is certainly the case with EF Core. Let's see how.

Calling DB Functions in LINQ Queries

So, some standard DB functions have been mapped to EF.Functions, as part of the DbFunctions class, but there are a big number of them that haven’t. Some examples include SQL Server's REVERSE, SOUNDEX, DIFFERENCE, and many others. Now, there's a way to call them in LINQ queries, but there is a (minor) caveat.

One way to do this is to have a method in your DbContext-derived class, which can be static or instance, such as:

public string Reverse(string property) => throw new NotImplementedException("Only to be called in an EF Core LINQ query.");
};

Now, we need to tell EF Core to use this function, and there are two ways:

  1. Applying an attribute
  2. Using code mapping

For option #1, it's as simple as:

[DbFunction(Name = "REVERSE", IsBuiltIn = true, IsNullable = true)]
public string Reverse(string column) ...

The key is the [DbFunction] attribute, we use it to tell EF Core that this method should map to a built-in function (IsBuiltIn) called (Name) "REVERSE" which can return nulls (IsNullable). If we weren't considering a built-in function, we should also specify a schema for it (Schema).

Option #2 involves adding some extra code to OnModelCreating:

modelBuilder.HasDbFunction(GetType().GetMethod(nameof(Reverse), [ typeof(string) ]))
    .IsBuiltIn(true)
    .IsNullable()
    .HasName("REVERSE");

I think this code is straightforward and can be easily mapped to the first example. The parameters to HasDbFunction are just a method pointer, everything else (return type, arguments) is inferred from it.

Using either approach, we can now call it as this:

var names = ctx.MyEntity.Select(x => ctx.Reverse(x.Name)).ToList();

But, used as we are to extension methods, we can do even better!

Consider this static class containing an extension method:

public static class DbContextExtensions
{
    public static string Reverse(this string property) => throw new NotImplementedException("Only to be called in an EF Core LINQ query.");

}

Don't worry about the exception being thrown, we're not going to call the method directly, it here just as a proxy to the database function we actually want to call, and we need to tell EF Core about it. However, you may find it weird, but the [DbFunction] approach won't work, we need to use HasDbFunction instead:

modelBuilder.HasDbFunction(typeof(DbContextExtensions).GetMethod(nameof(DbContextExtensions.Reverse), [ typeof(string) ]))

    .IsBuiltIn(true)
    .IsNullable()
    .HasName("REVERSE");

So, in a nutshell, for some reason, the extension method approach requires that we map the DB function using code! I wish Microsoft would fix this soon.

Conclusion
The ability to call DB functions from a LINQ query is quite powerful, we just need to remember a trick or two. Stay tuned for more!
Location: Coimbra, Portugal

Comments

Post a Comment

Popular posts from this blog

Audit Trails in EF Core

Introduction Audit trails is a common feature in database systems. Essentially, it's about knowing, for every table (or some selected ones), who created/updated each record, and when the creates/updates happened. It can also be useful to store the previous values too. Some regulations, such as SOX , require this for compliance. There are some libraries that can do this automatically in EF Core , and there's also the Change Data Capture in SQL Server (and possibly other databases too), of which I wrote about some time ago, but, hey, I wanted to write my own, so here is EFAuditable , my library for doing audit trails in EF Core. This one is designed with some extensibility in mind, so, please, read on. Scenarios EFAuditable  allows the following scenarios: Entities marked for auditing will have the audit columns added and persisted automatically to the database, on the same table Entities marked for history keeping will have their old values (in the case of updates or deletes) ...
Read more

.NET Cancellation Tokens

Introduction Cancellation tokens in .NET were introduced as part of the  .NET 4 Cancellation Framework . Essentially, it is a standard, cooperative, way to request for the cancellation of the execution of an operation. By cooperative it means that the code that uses it must abide to some rules, as opposed to the cancellation just stopping (killing) the execution. One example of cancellation is, on a web app, when the client closes the connection while the server is performing some long operation. In cases such as this, we may want to abort the operation, as we are not sending the results anywhere (or not, as we shall see). Because of this, ASP.NET Core allows us to add a parameter of type  CancellationToken  to our asynchronous actions, it is automatically associated with the client and is therefore signalled when the client connection is closed. A  CancellationToken  is, essentially, behind the scenes, a ManualResetEvent . Throughout this article I will either ...
Read more

ASP.NET Core Middleware

Image
Introduction This post is another of those "back to basics", still, there was one thing or two that I feel were not properly discussed elsewhere, hence this post. The ASP.NET Core Pipeline and Middleware You may know that ASP.NET Core defines a pipeline. This is where the incoming requests are processed and the response submitted. Each component in the pipeline - a middleware - can do things with the message (the request), such as check if it is authenticated and have rights to access the requested resource, turn the request into an MVC-style controller and action invocation, log it, and what not. You can read more about it here . The ASP.NET Core pipeline, from Microsoft The order by which the middleware is added to the pipeline matters. For example, the authorisation middleware must come after the authentication one, static files must come before routing, and exception handling must come before everything. The middleware order, from Microsoft A middleware is just a piece of...
Read more