35

I am using Entity Framework Code First in my current project. A Database consists of many tables, many views, and many functions. I am able to create tables using Entity Framework Code First. But I am unable to find a way to create stored procedures using Entity Framework Code First. I know Database-First strategy will fulfill my requirement and everything works well, but I don’t want to use Database-First strategy into my existing project code.

Please help me someone, what are the best ways to create stored procedures using Entity Framework Code First strategy?

wonea
  • 4,783
  • 17
  • 86
  • 139
sridharnetha
  • 2,104
  • 8
  • 35
  • 69

4 Answers4

32

Instead of using StringBuilder you can use existing EF methods

public override void Up() 
{
  CreateStoredProcedure(
    "MyStoredProcedure",
    p => new
    {
        id = p.Int()
    },
    @"SELECT some-data FROM my-table WHERE id = @id"
  );
}

public override void Down() 
{
  DropStoredProcedure("MyStoredProcedure");
}
Ruslan
  • 1,059
  • 12
  • 11
18

With Code First, you always make Migrations. Migrations inherits from DbMigration object, which has a very usefull method:

DbMigration.Sql(string)

Here are the steps:

1) Open the Package Manager Console from NuGet Package Manager

2) Type add-migration CreateHelloWorldStoredProcedureExample

3) The Visual Studio would show you a new class with two empty methods: Up and Down

4) In Up method, write your code, here is an example

 public override void Up()
 {
        StringBuilder storedProcedureCode = new StringBuilder();

        storedProcedureCode.Append("CREATE PROCEDURE dbo.HelloWorld" + Environment.NewLine);
        storedProcedureCode.Append("AS" + Environment.NewLine);
        storedProcedureCode.Append("BEGIN" + Environment.NewLine);
        storedProcedureCode.Append(@"SELECT 'Hello World'" + Environment.NewLine);
        storedProcedureCode.Append("END" + Environment.NewLine);

        this.Sql(storedProcedureCode.ToString());
}

While in Down method:

public override void Down()
    {
        this.Sql("DROP PROCEDURE dbo.HelloWorld ");
    }

After this, just run update-database and thats it!

Note: In case you use SQL Server, avoid the use of GO sentence.

Hamed
  • 117
  • 5
  • 17
Gabriel Andrés Brancolini
  • 1,038
  • 10
  • 15
  • BTW, Excuse me for my english. All corrections are wellcome! – Gabriel Andrés Brancolini Nov 23 '15 at 00:31
  • For the benefit of searchers, this works for .NET Core (entityframworkcore) too. This shows another way of getting it running in a means EF Core can use -> https://github.com/aspnet/EntityFramework/issues/245. You can then call it like: var blogs = _context.Blogs.FromSql("exec GetBlogForAuthorName @p0", "rod"); – JsAndDotNet Oct 28 '16 at 13:38
  • once update command execute then we can't update the procedure definition. – Shiraj Momin Jul 04 '17 at 05:57
  • 2
    `StringBuilder`has a method `AppendLine(..)` you might like. But probably even better would be to put your SQL code in a `string` variable affected with the `@` modifier to respect indentation and line breaks. Your SQL Code is static: no need to make it dynamically build at execution. – Askolein Sep 27 '19 at 15:14
  • There is one problem I had with creating sprocs from EF migrations. When I generate SQL script for migrations and run it on the database in my release pipeline, it fails with Incorrect syntax near the keyword 'PROC' because EF generates IF NOT EXISTS (check for migration) BEGIN (the code of the proc), but, by Microsoft's rules, CREATE statement must be the first one in a batch. To work around that, I have to use execute('my proc code here') in the migration `.Sql` code. – JustAMartin Jan 11 '23 at 15:20
12

If you are using EF Core 2.1, you can create stored procedure in such way:

public partial class AddStoredProcedure : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"your create procedure statement");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"your drop procedure statement");
    }
}
Viktors Telle
  • 763
  • 9
  • 22
  • There is one problem I had with this solution. When I generate SQL script for migrations and run it on the database in my release pipeline, it fails with `Incorrect syntax near the keyword 'PROC'` because EF generates `IF NOT EXISTS (check for migration) BEGIN (the code of the proc)`, but, by Microsoft's rules, `CREATE` statement must be the first one in a batch. To work around that, I have to use `execute('my proc code here')` in the migration Sql code. – JustAMartin Jan 11 '23 at 15:09
0

I wrote the following extension methods to simplify the creation or dropping of a procedure. At its core, the SqlFromFile extension method is used to read from a file that has been added to the project with "Copy to Output Directory" set to "Copy if newer". The file is read into a dynamic sql statement because of problems dotnet ef had when building a migration script.

In addition to extension methods for a procedure, the solution may be extended for use with user-defined functions, views, or other database objects.

    public static void SqlFromFile(this MigrationBuilder builder, string filename)
    {
        if (string.IsNullOrEmpty(filename))
            throw new ArgumentException("Required parameter is missing.", nameof(filename));

        FileInfo assemblyInfo = new FileInfo(Assembly.GetExecutingAssembly().Location);
        string fullPath = Path.Combine(assemblyInfo.Directory.FullName, filename);

        if (File.Exists(fullPath))
        {
            string sql = File.ReadAllText(fullPath);
            string lf = Environment.NewLine; // For compilation on Windows or Linux
            builder.Sql($"EXEC sp_executesql N'{lf}{sql.Replace("'", "''")}{lf}'");
        }
        else
        {
            throw new FileNotFoundException("File not found.", fullPath);
        }
    }

    public static void CreateProcedure(this MigrationBuilder builder, string name, string schema, string filename, bool dropIfExists = true)
    {
        if (string.IsNullOrEmpty(name))
            throw new ArgumentException("Required parameter is missing.", nameof(name));

        if (string.IsNullOrEmpty(filename))
            throw new ArgumentException("Required parameter is missing.", nameof(filename));

        if (dropIfExists) DropProcedure(builder, name, schema);

        SqlFromFile(builder, filename);
    }

    public static void DropProcedure(this MigrationBuilder builder, string name, string schema = null)
    {
        if (string.IsNullOrEmpty(name))
            throw new ArgumentException("Required parameter is missing.", nameof(name));

        if (string.IsNullOrEmpty(schema))
            schema = "dbo";

        builder.Sql($"IF OBJECT_ID('[{schema}].[{name}]', 'P') IS NOT NULL DROP PROCEDURE [{schema}].[{name}]");
    }
KrimblKrum
  • 183
  • 3
  • 11