Querying a Stored Procedure with EF Core

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.

1using System;
2using Microsoft.EntityFrameworkCore.Migrations;
3

4#nullable disable
5

6namespace MyApp.Database.Migrations
7{
8	public partial class ExportDataMigration : Migration
9	{
10		protected override void Up(MigrationBuilder migrationBuilder)
11		{
12			var sp = @"
13					CREATE PROCEDURE [dbo].[ExportData]	
14					AS
15					BEGIN
16

17					-- Add your script here
18					END	
19					GO";
20

21			migrationBuilder.Sql(sp);
22

23		}
24

25

26		protected override void Down(MigrationBuilder migrationBuilder)
27		{
28			migrationBuilder.Sql("DROP PROCEDURE [dbo].[ExportData]");
29		}
30	}
31}
32

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.

1namespace Database
2{
3    public partial class ExportData
4    {
5		public Guid ParticipantId { get; set; }
6		public DateTime StartTime { get; set; }
7		public DateTime? EndTime { get; set; }
8		public int? DurationSeconds { get; set; }
9		public int? LastQuestionViewed { get; set; }
10		public string? Source { get; set; }
11		public string? Country { get; set; }
12		public string? Age { get; set; }
13	}
14}
15

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.

1using Microsoft.EntityFrameworkCore;
2

3namespace Database
4{
5    public partial class MyContext : DbContext
6    {
7        public MyContext()
8        {
9        }
10

11        public MyContext(DbContextOptions<MyContext> options)
12            : base(options)
13        {
14        }
15

16        //... Other DbSets removed
17

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

20        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
21        {
22            //... other code removed for clarity
23        }
24

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

29            modelBuilder.Entity<ExportData>(entity =>
30            {
31                entity.HasKey(e => e.ParticipantId);
32            });
33

34            OnModelCreatingPartial(modelBuilder);
35        }
36

37        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
38    }
39}
40

Calling the stored procedure with EF Core

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

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