Querying a Stored Procedure with EF Core

If like me, you're a fan of EF Core using code first with migrations you may run into the scenario where you need to call a stored procedure to get some data. This doesn't happen particularly often, and in my case, the only reason I needed to do it was to be able to use SQL Servers PIVOT function to turn rows into columns which sadly at the time of writing can not be expressed in LINQ.

Adding the Stored Procedure with Migrations

Unlike regular table changes, you cannot define a stored procedure in C# and then have a tool create the migration file for you. That said, their not particularly hard to write yourself.

Create a migration file as follows with a method to create the stored proc and another to remove it for rollbacks.

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace MyApp.Database.Migrations
{
  public partial class ExportDataMigration : Migration
  {
  	protected override void Up(MigrationBuilder migrationBuilder)
  	{
  		var sp = @"
  				CREATE PROCEDURE [dbo].[ExportData]	
  				AS
  				BEGIN

  				-- Add your script here
  				END	
  				GO";

  		migrationBuilder.Sql(sp);

  	}


  	protected override void Down(MigrationBuilder migrationBuilder)
  	{
  		migrationBuilder.Sql("DROP PROCEDURE [dbo].[ExportData]");
  	}
  }
}

When EF Core calls the Stored Proc, it's going to want to have a type definition for the response. So create a model with the right properties. In my case it's as follows but yours will be different.

namespace Database
{
  public partial class ExportData
  {
  	public Guid ParticipantId { get; set; }
  	public DateTime StartTime { get; set; }
  	public DateTime? EndTime { get; set; }
  	public int? DurationSeconds { get; set; }
  	public int? LastQuestionViewed { get; set; }
  	public string? Source { get; set; }
  	public string? Country { get; set; }
  	public string? Age { get; set; }
  }
}

To avoid an error when calling the stored proc, we also need to add this model to our DbContext as a DbSet with a primary key, just don't make it a table.

using Microsoft.EntityFrameworkCore;

namespace Database
{
  public partial class MyContext : DbContext
  {
      public MyContext()
      {
      }

      public MyContext(DbContextOptions<MyContext> options)
          : base(options)
      {
      }

      //... Other DbSets removed

      public virtual DbSet<ExportData> ExportData { get; set; } = null!;

      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
          //... other code removed for clarity
      }

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          //... other code removed for clarity

          modelBuilder.Entity<ExportData>(entity =>
          {
              entity.HasKey(e => e.ParticipantId);
          });

          OnModelCreatingPartial(modelBuilder);
      }

      partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
  }
}

Calling the stored procedure with EF Core

To call the stored procedure we use the FromSqlRaw method on the DbSet in the content.

var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
Tagged: