How to add stored procedures to your EF Migrations

Did you know you can easily add stored procedures to your EF Migrations? 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 to your migrations with just a bit of extra effort.

See the source for this article here.

First, add your created scripts as project resources. I added my two scripts under a directory 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 =
                assembly.GetManifestResourceNames().
                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();
            migrationBuilder.Sql(sql);
        }
    }
}

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 your 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.

Originally published February 7, 2019. Information refreshed November 9, 2022.