31

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,425
  • 17
  • 82
  • 137
sridharnetha
  • 1,778
  • 6
  • 30
  • 59

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,049
  • 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,009
  • 10
  • 14
  • 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
  • 1
    `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
9

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
  • 713
  • 9
  • 19
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
  • 163
  • 2
  • 11