C# developers frequently use Entity Framework Core (EF Core) to interact with databases. However, EF Core has limitations when it comes to handling complex data structures returned by stored procedures. While Microsoft’s Entity Framework (EF) Core provides mapping capabilities, EF Core struggles with:

• Limiting stored procedure output to a single Entity type.
• Inability to process complex types resulting from JOIN operations.
• Lack of support for create, update, and delete actions.

To overcome these challenges, we can leverage a combination of C#, .NET, Microsoft SQL Server, and EF Core. This workaround is adaptable to any .NET-supported database and language compatible with EF Core, as long as the necessary utility code is translated accordingly. Let’s explore a sample stored procedure to demonstrate how minor adjustments can address these EF Core constraints.

Illustrative Stored Procedure Returning a Complex Result

Consider a stored procedure named GetEmployeesWithDepartment. This procedure fetches a complex result set by combining data from two related tables: Employee and Department:

The Employee table has a self-referencing relationship through the ManagerId field, establishing a foreign key. It also connects to the Department table via the Employee.DepartmentId field, linked to the Department table’s Id column. These table relationships are defined as:

1

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

The GetEmployeesWithDepartment stored procedure retrieves an Employee record based on a given Employee.Id input parameter. It returns the Id and associated data, such as the employee’s Department and Name:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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 assume we need to find the department of the first employee listed in a simple test database (in this case, John from Engineering). We aim to execute this stored procedure directly from our C# code. To achieve this, we need to configure EF Core to handle calls to GetEmployeesWithDepartment as a parameterized stored procedure.

Note: Before you begin, ensure you’ve already scaffold your database using either the Scaffold-DbContext command in the Package Manager Console or the dotnet ef dbcontext scaffold command in .NET Core CLI.

Step 1: Defining the Stored Procedure’s Result Set Model

First, we create a file named GetEmployeesWithDepartment_Result.cs to define the structure of our complex return type:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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; }
}

When working with Microsoft SQL Server, we can confirm the stored procedure’s result column types using the sp_describe_first_result_set command:

1

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

This command reveals the stored procedure’s columns along with their respective data types. With the result type defined, we proceed to integrate it into our EF model.

Step 2: Incorporating the Model into the DbContext File

Next, we integrate our result model into the EF Core DbContext file of our application. EF Core provides a structured method for extending the application’s data model. This is accomplished through an extension is supported using partial classes, specifically by implementing the OnModelCreatingPartial method. To prevent EF Core’s scaffolding tools from overwriting our custom code, we add our result model to a partial C# class named EFCoreSPContext.SP.cs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

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 a snippet of the EFCoreSPContext.SP.cs file in our repository. Now, we need to add code to define the primary key for our model, if applicable.

Step 3: Specifying the Model’s Key

We use the OnModelCreatingPartial method within our EFCoreSPContext definition to specify whether our stored procedure’s result set includes a key value.

If a key value exists, we utilize the HasKey method to explicitly define the property representing that key:

1
2
3
4
5

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

Conversely, if our entity lacks a key value, we employ the HasNoKey method:

1
2
3
4
5

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

With this, our model definition is complete. We are now prepared to call the stored procedure and retrieve our sample employee data.

Effortlessly Calling Complex Stored Procedures

To simplify the process of calling our stored procedure, we introduce a public method to the EFCoreSPContext file. This method accepts the Employee.Id value, passes it to the stored procedure, and retrieves the resulting complex data as a list:

1
2
3
4
5
6
7

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

Our DbContext file is now equipped to execute stored procedures and handle complex result sets. Our code is complete is now ready for implementation. Revisiting our initial query, we can retrieve the department and other associated data for the first employee in our database using a straightforward command:

1

var employees = dbContext.SP_GetEmployeesWithDepartment(1);

In conclusion, we’ve implemented an efficient and elegant solution to retrieve non-database entities from stored procedures. This approach requires minimal supporting code while providing substantial benefits when utilizing EF Core to manage complex result sets.

The Toptal Engineering Blog editorial team extends its sincere appreciation to Alexander Skogorev for his invaluable contributions in reviewing the technical aspects and code examples presented in this article.