Question: 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?

Answer: 
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.
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}]");
    }

```