Create Stored Procedures using Entity Framework Code First?

user_2463112 0
Viewed 39764

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?

ef-code-first sql-server
4 Answers

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");
    }
}
user_28046210

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}]");
    }
user_43015790
Related