EF Core 5: Mapping user-defined functions

Mapping user-defined functions with EF Core 5

Want to learn EF Core 5 Mapping? In this article, you'll learn how to map to scalar functions and table-valued functions (TVFs) in your DbContext.

Author: Julie Lerman


In my “Getting Started with EF Core 5” course, I discuss some of the most common ways to map your entities to a relational database—namely, through tables and views—as well as how to make raw SQL calls and capture results in entity types.

There are some other ways to map entities to the database that you should be aware of and may be able to benefit from. In this article, you’ll learn how to map to scalar functions and table-valued functions (TVFs) in your DbContext.

I’ll be working with a simple model of Customers, Orders, LineItems and Products. If you want to test this out, you can find the sample application along with migrations for seeding data and creating the relevant functions in the database in my GitHub repository here.

I’m using SQL Server as my database.

Mapping your own scalar functions

I’ve created a scalar function in my database called TotalSpentByCustomer which takes in the customer’s ID and returns a sum of all orders that customer has ever placed.

The function takes an int (customerId) as a parameter. Here is the core TSQL of that function:

SELECT @TotalSpent = sum(LineItem.Quantity * Product.UnitPrice)
FROM LineItem
INNER JOIN Orders ON LineItem.OrderId = Orders.OrderId
INNER JOIN Product ON LineItem. ProductId = Product.ProductId
WHERE Orders.customerid = @CustomerId

That’s nice to have, rather than working out the LINQ query logic in any query that you’d like to include that bit of data.

Mapping scalar functions is not new to EF Core 5, but may be new to you. It’s a two-step setup. First, you need to let your application know that the function exists. That means defining a method either directly in the DbContext class or, if it’s common across multiple DbContexts in your solution, in a static method in your application:

public int TotalSpentByCustomer(int customerId)
  => throw new NotSupportedException();

The method throws an exception to prevent it being used elsewhere in code. It can only be used by EF Core.

The next step is to map this method in OnModelCreating as a DbFunction, using the ModelBuilder.HasDbFunction method.

modelBuilder.HasDbFunction(typeof(SalesContext)
  .GetMethod(nameof(TotalSpentByCustomer),
             new[] { typeof(int) }))
  .HasName("TotalSpentByCustomer");

Now you can use the method in your queries. For example:

private static List<CustWithTotal> RunScalarFunction()
{

    using var _context = new SalesContext();
    return _context.Customers.Select
      (c => new CustWithTotal(c.Name,
                              context.TotalSpentByCustomer(c.Id))
      ).ToList();
}

This returns a list of a locally defined struct named CustWithTotal. Easy!

Mapping table-value functions

Next up is mapping to a table-valued function (TVF), which returns a structured table rather than a single scalar value.

(Fun fact: TVF support is a feature created by Paul Middlelton, a member of the developer community, via a pull-request to EF Core 5. The EF Core team is always eager to work with authors of PRs to bring new features and fixes into the framework!)

I’ll add a simple TVF to the database which returns both the customer name and total. I defined it to get all customers, although I could have specified that parameter for a single customer’s ID if I wanted.

CREATE FUNCTION CustomerNameAndTotalSpent()
RETURNS TABLE
AS
RETURN
(
   select customers.name,dbo.TotalSpentbyCustomer as TotalSpent
   from Customers
)

I can compose SQL queries from the TVF—only using the two columns of course—such as:

select * from dbo.CustomerNameAndTotalSpent() where TotalSpent>100

To map this TVF, you need to define the method for the mapping as you did for the scalar function. However, in this case, you don’t have the option of placing it in a static class. That’s because it relies on the DbContext method FromExpression. I’m giving a different name to the CLR function, which is NameAndTotalSpentByCustomer:

public IQueryable<CustWithTotalClass> NameAndTotalSpentByCustomer()
    => FromExpression(() => NameAndTotalSpentByCustomer());

Notice that the method returns CustWithTotalClass. I wanted to highlight that I can’t use the struct that I created when returning a type composed of a string and the returned int from the scalar function. It has to be a class because it has to be an entity. Although, in my case, it is a keyless entity. More on that in a moment.

Have a look at the difference between the struct and the class. Some of the differences are intentional for educational purposes:

public struct CustWithTotal
{
    public CustWithTotal(string name, int total)
    {
        Name = name;
        TotalSpent = total;
    }
    public string Name { get; }
    public int TotalSpent { get; }
}

public class CustWithTotalClass
{
    public CustWithTotalClass(string name, int totalSpent)
    {
        Name = name;
        TotalSpent = totalSpent;
    }
    public string Name { get; private set; }
    public int TotalSpent { get; private set; }
}

They both have constructors that take in the needed values, but because the class is mapped to the TVF, the properties must match the column names specified in the TVF. And because the class is recognized by EF Core as an entity in the DbContext model, it must follow the rule of parameterized constructors, which requires the parameter names to match the property names. The struct is not mapped, and we use it by passing in a string and an int. Therefore, you have more flexibility with naming the parameters and the properties.

With the expression defined, I can now map that expression to the TVF in the database. I can do so with the simplest syntax, although HasDbFunction does provide for additional configurations if needed:

modelBuilder.HasDbFunction(typeof(SalesContext)
    .GetMethod(nameof(NameAndTotalSpentByCustomer)))
    .HasName("CustomerNameAndTotalSpent");

One last configuration is to make sure that the DbContext knows my new type is a keyless entity:

modelBuilder.Entity<CustWithTotalClass>().HasNoKey();

It’s all wired up now. I can retrieve all of the customers with their totals in a linq query:

context.NameAndTotalSpentByCustomer().ToList();

Or I can compose on the function. For example:

_context.NameAndTotalSpentByCustomer().Where(c=>c.TotalSpent>100).ToList();

This generates SQL that composes directly on top of the TVF:

SELECT [c].[Name], [c].[TotalSpent]
FROM [dbo].[CustomerNameAndTotalSpent]() AS [c]
WHERE [c].[TotalSpent] > 100

Paul Middleton, the creator of this feature, shared a great use case for TVFs with EF Core of self-referencing queries, which are much easier to express in SQL. A canonical example of a self-referencing query is an employee table which also has a managerID column to indicate another employee who is the manager of the target employee.

He wrote up a nice description of this which was added to the team’s blog post Announcing Entity Framework Core 5.0 Preview 8.

Once again, choose the right tool for the job

While EF Core does a great job of creating SQL for most queries and data movement (add, updates and deletes), there will always be times when you can either express something more simply or experience better performance with your own SQL. Having the ability to map to your own user-defined functions is another way that EF Core allows you to benefit from raw SQL. You can learn more about other raw sql features of EF Core 5 in my course EF Core: Getting Started.

You can find the code for the sample in this article here.



Related tags:

data professional   entity framework   entity framework core  
About the author

Julie Lerman is a software coach with 30 years of experience as a developer. She is a long-time Pluralsight author, Microsoft MVP, Microsoft Regional Director and Docker Captain who lives in the hills of Vermont and continues to be known as the world’s top expert in Entity Framework. You can find Julie giving keynotes and hard-core coding sessions on Domain-Driven Design, Azure, Entity Framework and other topics at software conferences around the world. As a coach, Julie provides support for DDD and architecture implementation, facilitates Event Storming workshops and provides deep dives into Entity Framework for her clients. Julie has authored the highly acclaimed "Programming Entity Framework" books from O’Reilly, the MSDN Magazine Data Points column and numerous articles in your favorite programming resources.

10-day free trial

Sign Up Now