Ejecutar procedimientos almacenados básicos del marco de entidades complejas

Los desarrolladores de .NET a menudo necesitan llamar a un procedimiento almacenado (SP) de base de datos desde su capa de servidor C#. Entity Framework (EF) Core de Microsoft se puede usar para asignar o importar SP como funciones, pero lamentablemente EF Core no admite de forma nativa la recuperación de resultados complejos de procedimientos almacenados. Esto se debe a las limitaciones de la solución lista para usar de EF Core que:

• Restringir el resultado de un procedimiento almacenado a Entity Tipo.
• Un tipo complejo no se puede devolver en respuesta a un JOIN Dominio.
• Deshabilite las operaciones de creación, actualización y eliminación.

Podemos solucionar estas limitaciones usando C#, .NET, Microsoft SQL Server y EF Core juntos. Esta solución alternativa se puede usar con cualquier base de datos compatible con .NET o lenguaje .NET compatible con EF Core, siempre que el código de la utilidad esté traducido a ese idioma. Veremos un procedimiento almacenado de ejemplo para ver cómo unos pocos ajustes simples pueden superar las limitaciones de EF Core.

Un procedimiento almacenado hipotético con un resultado complejo

Pensemos GetEmployeesWithDepartmentun procedimiento almacenado que devuelve un resultado complejo que contiene información de dos tablas de bases de datos relacionadas, Employee Y Department:

El Employee La tabla se referencia a sí misma mediante una clave externa desde su ManagerId Campo. También se refiere a la Department mesa de la Employee.DepartmentId campo conectado a la Department Mesa Id Dividir. Las relaciones de orden entre estas tablas son:

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

Ahora echemos un vistazo GetEmployeesWithDepartmentun SP que devuelve un Employee Fila de la tabla correspondiente al parámetro de entrada Employee.Id. Nuestro SP los devuelve Id valor y toda la información relacionada, como B. la del empleado Department Y Name Valores:

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

Supongamos que queremos encontrar el departamento asociado con el primer empleado enumerado en una base de datos de prueba simple (en nuestro ejemplo, el primer empleado enumerado es John en Ingeniería). Queremos ejecutar este SP desde nuestro código C#, así que configuremos EF Core para admitir llamadas GetEmployeesWithDepartment como un SP parametrizado.

Nota: Antes de continuar, scaffold tu base de datos usando Scaffold-DbContext comando en la consola del administrador de paquetes o el dotnet ef dbcontext scaffold Comando en .NET Core CLI.

Paso 1: Cree un modelo de conjunto de resultados de procedimiento almacenado

Primero creamos un archivo llamado GetEmployeesWithDepartment_Result.cs y defina la estructura para nuestro tipo de retorno complejo:

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

Usando Microsoft SQL Server como servidor de base de datos, podemos verificar explícitamente los tipos de columna de resultados SP ejecutando el sp_describe_first_result_set Dominio:

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

Este comando muestra las columnas del procedimiento almacenado y la lista de tipos asociados. Con el tipo de resultado definido, procedamos a actualizar nuestro modelo EF.

Paso 2: Pegue el modelo en el DbContext archivo

Estamos listos para integrar el modelo de resultados en EF Core de nuestra aplicación DbContext Archivo. EF proporciona un enfoque elegante para ampliar el modelo de datos de una aplicación. Tal extensión es compatible con clases parciales y, en particular, mediante el uso de un OnModelCreatingPartial Método. Para evitar que las herramientas de scaffolding de EF Core cambien nuestro código personalizado, agreguemos nuestro modelo de resultados EFCoreSPContext.SP.csuna clase parcial de C#:

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

Así es cómo EFCoreSPContext.SP.cs búsquedas en nuestro repositorio. Ahora necesitamos agregar un código que identifique la clave principal de nuestro modelo, si existe.

Paso 3: Proporcione la clave del modelo

Indicamos si el conjunto de resultados de nuestro SP tiene un valor clave configurando nuestro modelo en un OnModelCreatingPartial método en nuestro EFCoreSPContext Definición.

Si nuestro conjunto de resultados tiene un valor clave, usamos el HasKey método para identificar explícitamente la propiedad asociada con este valor clave:

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

Si nuestra entidad no tiene un valor clave, lo usamos HasNoKey método en su lugar:

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

Nuestra definición de modelo ya está completa. Estamos listos para llamar al SP y obtener nuestros datos de empleados de ejemplo.

Llamar a procedimientos almacenados complejos: fácil como 1-2-3

Para facilitar la llamada a nuestro SP, agreguemos otro método público al EFCoreSPContext Archivo. La definición del método acepta la Employee.Id valor proporcionado, pasa eso Id al SP y recupera los resultados complejos generados como una lista:

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

Nuestro DbContext El archivo ahora está listo para llamar a un procedimiento almacenado y devolver un conjunto de resultados de tipo complejo, y nuestro código está completo. Volviendo a nuestra consulta de ejemplo, podemos usar un comando simple para devolver el departamento y otros datos asociados con el primer empleado en nuestra base de datos:

var employees = dbContext.SP_GetEmployeesWithDepartment(1);

Aplicamos una solución simple pero inteligente y poderosa para devolver una entidad que no es de base de datos desde un procedimiento almacenado. Este enfoque requiere relativamente pocas líneas de código de soporte y vale la pena significativamente cuando se usa EF Core para recuperar resultados complejos.

El equipo editorial del Blog de Ingeniería de Toptal quiere agradecerle alexander skogorev por revisar el contenido técnico y los ejemplos de código presentados en este artículo.

Lectura adicional en el blog de ingeniería de Toptal: