Creating Stored Procs in EF Migrations


07 Feb 2019 | Steve Hickman

EF code-first doesn’t expect you to have stored procedures; so, you won’t find any support for creating sprocs through migrations. However, you can add them into your migrations with just a bit of extra effort.

See the source for this article here.

First, add your create scripts as project resources. I added my two scripts under a directory I called SQL.

Second, add an empty migration with this command:

dotnet ef migrations add StoredProcs

Add the code you see in the Up method in 20181227202527_StoredProcs.cs.

protected override void Up(MigrationBuilder migrationBuilder)
    var assembly = Assembly.GetExecutingAssembly();
    var resourceNames =
                Where(str => str.EndsWith(".sql"));
    foreach (string resourceName in resourceNames)
        using (Stream stream = assembly.GetManifestResourceStream(resourceName))
        using (StreamReader reader = new StreamReader(stream))
            string sql = reader.ReadToEnd();

This code finds all resources that end in “.sql” and runs each of them. Because I am simply generating all of my sprocs, I don’t really care what order they run. However, you could create a migration for each script or run multiple scripts in a migration, but refer to them explicitly.

To make the SQL scripts embedded resources, you can right-click them in Visual Studio, go to properties, and change to embedded resources. If you are not using Visual Studio, just edit the .csproj file and make sure they are included like this:

Finally, run the migrations:

dotnet ef database update 

Now you’ve got you up-to-date schema and your sprocs. You could use this technique for anything that doesn’t fit under schema change–initial seed data, data migration, etc.

Related Posts

Sign Up for Our Blog