An Expert Workaround for Executing Complex Entity Framework Core Stored Procedures

Microsoft’s Entity Framework Core is a popular object-relational mapper, but it doesn’t support the return of complex type results from stored procedures. A clever bit of code gets us around this limitation, returning non-database entities with ease.


Toptalauthors are vetted experts in their fields and write on topics in which they have demonstrated experience. All of our content is peer reviewed and validated by Toptal experts in the same field.

Microsoft’s Entity Framework Core is a popular object-relational mapper, but it doesn’t support the return of complex type results from stored procedures. A clever bit of code gets us around this limitation, returning non-database entities with ease.


Toptalauthors are vetted experts in their fields and write on topics in which they have demonstrated experience. All of our content is peer reviewed and validated by Toptal experts in the same field.
Pankaj Kansodariya
Verified Expert in Engineering

Pankaj is a back-end developer and Microsoft Certified Professional with more than 18 years of experience within the Microsoft ecosystem, including C#, VB.NET, SQL Server, and cloud computing with Microsoft Azure. He has worked as a .NET developer at companies including Granicus, Gartner, and Jacobs.

Expertise

PREVIOUSLY AT

Gartner
Share

.NET developers often need to call a database stored procedure (SP) from their C# server layer. Microsoft’s Entity Framework (EF) Core can be used to map or import SPs as functions but, unfortunately, EF Core doesn’t natively support the retrieval of complex results from stored procedures. This is due to limitations in EF Core’s out-of-the-box solution that:

  • Restrict a stored procedure’s result to an Entity type.
  • Cannot return a complex type in response to a JOIN command.
  • Make create, update, and delete operations unavailable.

We can get around these restrictions by using C#, .NET, Microsoft SQL Server, and EF Core together. This workaround can be used with any .NET-supported database or .NET language that supports EF Core, provided the utility code is translated into that language. We’ll look at an example stored procedure to see how a few simple adjustments can overcome EF Core’s constraints.

A Hypothetical Stored Procedure With a Complex Result

Let's consider GetEmployeesWithDepartment, a stored procedure that returns a complex result containing information from two related database tables, Employee and Department:

Two related database tables from which a stored procedure that returns a complex result containing information could come.

The Employee table references itself through a foreign key from its ManagerId field. It also references the Department table from the Employee.DepartmentId field connected to the Department table’s Id column. The ordinal relationships between these tables are:

Relationships = Employee(1) : Department(1) and Department(1) : Employees(N)

Now let’s look at GetEmployeesWithDepartment, an SP that returns an Employee table row matching the input parameter Employee.Id. Our SP returns the Id value and all of its associated information, such as the employee’s Department and Name values:

CREATE OR ALTER PROCEDURE [dbo].[GetEmployeesWithDepartment] 	
    @id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [E].*, [D].[Name] AS [Department]
    FROM [dbo].[Employee] [E]
        INNER JOIN [dbo].[Department] [D] ON [E].[DepartmentId] = [D].[Id]
    WHERE [E].[Id] >= @id
END

Let’s say we want to determine the department associated with the first employee listed in a simple test database (in our example, the first employee listed is John in Engineering). We would like to execute this SP from our C# code, so let’s configure EF Core to support calling GetEmployeesWithDepartment as a parameterized SP.

Note: Before you proceed, scaffold your database using the Scaffold-DbContext command in the Package Manager Console or the dotnet ef dbcontext scaffold command in .NET Core CLI.

Step 1: Create a Stored Procedure Result Set Model

First, we’ll create a file called GetEmployeesWithDepartment_Result.cs and define the structure for our complex return type:

public class GetEmployeesWithDepartment_Result
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
    public int? ManagerId { get; set; }
    public int Salary { get; set; }
    public decimal? Bonus { get; set; }
    public string Department { get; set; }
}

Using Microsoft SQL Server as the database server, we can explicitly verify the SP result column types by executing the sp_describe_first_result_set command:

EXEC sp_describe_first_result_set N'[dbo].[GetEmployeesWithDepartment]'

This command displays the stored procedure’s columns and associated type list. With the result type defined, we move on to updating our EF model.

Step 2: Include the Model in the DbContext File

We are ready to incorporate the result model into our application’s EF Core DbContext file. EF provides an elegant approach to extending an application’s data model. Such an extension is supported with partial classes and—specifically—by using an OnModelCreatingPartial method. To keep EF Core’s scaffolding tools from modifying our custom code, we’ll add our result model to EFCoreSPContext.SP.cs, a partial C# class:

using EFCoreSP.Data.SPs;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace EFCoreSP.Data
{
    public partial class EFCoreSPContext : DbContext
    {
        public virtual DbSet<GetEmployeesWithDepartment_Result>
            GetEmployeesWithDepartment_Results { get; set; }

        // We’ll add subsequent changes here
    }
}

Here’s how EFCoreSPContext.SP.cs looks in our repository. We now need to add code that identifies our model’s primary key, if one is present.

Step 3: Specify the Key of the Model

We’ll indicate whether our SP’s result set has a key value by configuring our model in an OnModelCreatingPartial method in our EFCoreSPContext definition.

If our result set has a key value, we use the HasKey method to explicitly identify the property associated with that key value:

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<GetEmployeesWithDepartment_Result>(entity => 
        entity.HasKey(e => e.Id));      
}

If our entity has no key value, we use the HasNoKey method instead:

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<GetEmployeesWithDepartment_Result>(entity => 
        entity.HasNoKey());       
}

Our model definition is now complete. We’re ready to call the SP and retrieve our example employee data.

Calling Complex Stored Procedures: Easy As 1-2-3

To simplify calling our SP, we’ll add one more public method to the EFCoreSPContext file. The method’s definition accepts the Employee.Id value provided, passes that Id to the SP, and retrieves the generated complex results as a list:

public IEnumerable<GetEmployeesWithDepartment_Result> 
    SP_GetEmployeesWithDepartment(int id)
{
    return this.GetEmployeesWithDepartment_Results
        .FromSqlInterpolated($"[dbo].[GetEmployeesWithDepartment] {id}")
        .ToArray();
}

Our DbContext file is now ready to call a stored procedure and return a complex type result set, and our code is complete. Returning to our example query, we can use a simple command to return the department and other data associated with the first employee in our database:

var employees = dbContext.SP_GetEmployeesWithDepartment(1);

We applied a simple, yet clever and powerful, solution to return a non-database entity from a stored procedure. This approach entails relatively few lines of supporting code and yields a considerable payoff when using EF Core to retrieve complex results.

The editorial team of the Toptal Engineering Blog extends its gratitude to Alexander Skogorev for reviewing the technical content and code samples presented in this article.


Understanding the basics

  • What is Entity Framework (EF) Core used for?

    Microsoft ported EF from .NET Framework to .NET Core. With its lightweight architecture that allows developers to extend the cross-platform framework, EF Core is a popular tool for accessing a data layer from a .NET language, usually C#.

  • What is the difference between EF and EF Core?

    EF is an object-relational mapper designed for the .NET Framework. Whereas EF is no longer actively developed, EF Core is Microsoft’s current object-database mapper for .NET (i.e., .NET Core).

  • Does EF Core support stored procedures?

    Yes, EF Core supports stored procedures, much like its predecessor, Entity Framework.

  • How do I run a stored procedure in EF Core?

    You may execute a stored procedure in EF Core by using the DbSet.FromSql() or DbContext.Database.ExecuteSqlCommand() command.

  • Can I call stored procedures in Entity Framework?

    Yes, a stored procedure can be imported as a function with Entity Framework in an .edmx file.

Hire a Toptal expert on this topic.
Hire Now
Pankaj Kansodariya

Pankaj Kansodariya

Verified Expert in Engineering

London, United Kingdom

July 13, 2020

About the author

Pankaj is a back-end developer and Microsoft Certified Professional with more than 18 years of experience within the Microsoft ecosystem, including C#, VB.NET, SQL Server, and cloud computing with Microsoft Azure. He has worked as a .NET developer at companies including Granicus, Gartner, and Jacobs.

authors are vetted experts in their fields and write on topics in which they have demonstrated experience. All of our content is peer reviewed and validated by Toptal experts in the same field.

Expertise

PREVIOUSLY AT

Gartner

World-class articles, delivered weekly.

Subscription implies consent to our privacy policy

World-class articles, delivered weekly.

Subscription implies consent to our privacy policy

Join the Toptal® community.