EF6: Create stored procedure. Use Fluent API or DBMigrations?

Question

I'm using EF6 code first to create my database. I understand the syntax, the DbContext, and the modelbuilder. I use LINQ for several exhaustive queries and everything works fine.

But now I need to do something that can't be done in one query using linq. I need to perform a Merge statement using a stored procedure.

I've seen several questions on how to create a stored procedure, like: Create Stored Procedures using Entity Framework Code First?

Most answers are talking about creating a derived class for DbMigrations and overriding the Up() function. I understand what I should write in the Up function to make sure the stored procedure is created.

But what should I do to make that during database creation this Up function is called?

Should I do something in DbContext.OnModelCreating?

I don't think I should instantiate the subclass of DbMigrations and call Up().

The link mentioned above is talking about "Open the Package Manager Control". What is that? Or do you really use this method when migrating from an older version to a newer one?

Answer 1

After some investigation I found how to make sure that a stored procedure is created whenever the database is created.. I found two methods, each with their own advantages and disadvantages. Hence I describe them both. Sorry if this makse the answer fairly long.

The two methods described here are:

• Create a DataBase Initializer, a class that implements IDataBaseInitializer. This will probably be a class derived from DropCreateDatabaseIfModelChanges or similar. Override the Seed function and create in this function the stored procedure using context.Database.ExecuteSqlCommand(...).
• Use Entity Framework migrations for the creation of stored procedures.

The first method is simpler. Whenever the database is created, the Seed is called and the stored procedure is created. However this method has the disadvantage that whenever the name or the type of the parameters of the stored procedure change, this is not detected until runtime.

The DbMigration method matches the parameters of the stored procedure using a lambda expression, so whenever the type or the name of the parameter changes, the compiler detects if the definition of the remote procedure matches the parameter.

I'll describe both methods. Both examples have the same simple Hello World! procedure and a big Merge procedure with a lot of parameters.

The definition of the merge statement is not really important. What it does is that it checks if there is already a record matching several properties, and if so it adds costs to the existing costs. If not it creates a record and initializes the costs with costs. This is a typical example where using linq statement and IQueryable wouldn't suffice. Using linq, one would have to retrieve the record, update it and call SaveChanges, with the problems (1) that in the meantime someone else might have added a value and (2) it needs at least two roundtrips. Hence the need for a stored procedure.

Method IDatabaseInitializer

In your project you create the entity classes and a class derived form DbContext with DbSet properties for the database tables you want to access.

For example:

public class UsageCosts
{
    public int Id {get; set; }
    public DateTime InvoicePeriod { get; set; }
    public long CustomerContractId { get; set; }
    public string TypeA { get; set; }
    public string TypeB { get; set; }
    public decimal VatValue { get; set; }

    // the value to invoice
    public decimal PurchaseCosts { get; set; }
    public decimal RetailCosts { get; set; }
}

public class DemoContext : DbContext
{
    public DemoContext(string nameOrConnectionString) : base(nameOrConnectionString) {}

    public DbSet<UsageCosts> UsageCosts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // add entity framework fluent api statements here          
    }
}

Apart from your database classes, create a database initializer, It has a function Seed that will be called when the database is created.

internal class DataBaseInitializer : DropCreateDatabaseIfModelChanges<DemoContext>
{
    protected override void Seed(DemoContext context)
    {
        base.Seed(context);
        
        // create stored procedures here
        this.CreateStoredProcedureHelloWorld(context)
        this.CreateStoredProcedureUpdateUsageCosts(context)
    }

Simple example that shows how to create a Stored Procedure (Hello World!)

    private void CreateStoredProcedureHelloWorld(DemoContext context)
    {
        context.Database.ExecuteSqlCommand("create procedure HelloWorld as begin Select 'Hello World' end;");
    }

Create a stored procedure with input parameters:

    private void CreateStoredProcedureUpdateUsageCosts(DemoContext context)
    {
        var x = new StringBuilder();
        x.AppendLine(@"create procedure updateusagecosts");
        x.AppendLine(@"@InvoicePeriod datetime,");
        x.AppendLine(@"@CustomerContractId bigint,");
        x.AppendLine(@"@TypeA nvarChar(80),");
        x.AppendLine(@"@TypeB nvarChar(80),");
        x.AppendLine(@"@VatValue decimal(18, 2),");
        x.AppendLine(@"@PurchaseCosts decimal(18, 2),");
        x.AppendLine(@"@RetailCosts decimal(18, 2)");
        x.AppendLine(@"as");
        x.AppendLine(@"begin");
        x.AppendLine(@"Merge [usagecosts]");
        x.AppendLine(@"Using (Select @InvoicePeriod as invoicePeriod,");
        x.AppendLine(@"              @CustomerContractId as customercontractId,");
        x.AppendLine(@"              @TypeA as typeA,");
        x.AppendLine(@"              @TypeB as typeB,");
        x.AppendLine(@"              @VatValue as vatvalue)");
        x.AppendLine(@"              As tmp ");
        x.AppendLine(@"On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod");
        x.AppendLine(@"AND [usagecosts].[customercontractId] = tmp.customercontractid");
        x.AppendLine(@"AND [usagecosts].[typeA] = tmp.typeA");
        x.AppendLine(@"AND [usagecosts].[typeB] = tmp.typeB");
        x.AppendLine(@"AND [usagecosts].[vatvalue] = tmp.Vatvalue)");
        x.AppendLine(@"When Matched Then ");
        x.AppendLine(@"    Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts,");
        x.AppendLine(@"               [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts");
        x.AppendLine(@"When Not Matched Then");
        x.AppendLine(@"    Insert (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)");
        x.AppendLine(@"    Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);");
        x.AppendLine(@"end");
        context.Database.ExecuteSqlCommand(x.ToString());
    }
}

The hello world example can be found here on StackOverflow

The method with the StringBuilder can also be found somewhere on StackOverflow, but alas I can't find it.

During creation of the database DatabaseInitializer.Seed(...) is called. Here the context is ordered to perform an SQL statement. This statement is a string. That's why the compiler won't notice changes in the name or the type of the parameters of the functions.

DbMigration methods

For migrations see:

• MSDN: Enabling Migrations
• Creating and Calling Stored Procedure from Entity Framework 6 Code First

The idea is to let the visual studio package manager create a derived class of DbManager which has an Up() function. This function will be called whenever the database is migrated upwards to the version for the derived class.

Inside the Up() you can call the base class DbMigration.CreateStoredProcedure. The nice thing about this method would be that the translation from Entity type to parameters is done using delegates (with a lambda expression) and thus checked at compile time: do the properties still exist and do they have the correct type?

Alas it is not enough to construct the derived class from DbMigration, and call the Up() functions from within your Seed() function.

To make sure that the Up() function is called it is the easiest to let visual studio do this.

• Create your project
• Add Nuget package for entity framework
• Create your entity classes and your DbContext with DbSet properties for the entity classes
• In visual studio start the Nuget Package Manager Console via the Tools menu
• Using the Nuget Package Manager Console enable migrations using the command Enable-Migrations
• Using the Nuget Package Manager Console add one migration and give if a name, for instance InitialCreation using the command add-Migration InitialCreation

You'll notice that several classes are added to your project.

• Configuration derived from DbMigratinConfiguration with a function Seed()
• InitialCreation derived from DbMigration with a function Up() (and a function Down(). In this Up you'll see one or more CreateTable functions

If you still have a database seeder class as described in the previous example, and you use DataBase.SetInitializer to initialize it, then whenever the database needs to be re-created the various Up() and Seed() functions are called in the following order:

• Constructor of Configuration
• InitialCreation.Up()
• DatabaseSeeder.Seed()

For some reason Configuration.Seed() is not called.

This gives us the opportunity to create the stored procedures in InitialCraeation.Up()

public override void Up()
{
    CreateTable("dbo.UsageCosts",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                InvoicePeriod = c.DateTime(nullable: false),
                CustomerContractId = c.Long(nullable: false),
                TypeA = c.String(),
                TypeB = c.String(),
                VatValue = c.Decimal(nullable: false, precision: 18, scale: 2),
                PurchaseCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
                RetailCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
            })
        .PrimaryKey(t => t.Id);
}

The "Hello World" Stored procedure is created as follows:

    base.CreateStoredProcedure("dbo.HelloWorld3", "begin Select 'Hello World' end;");

The stored procedure with input parameters:

    base.CreateStoredProcedure("dbo.update2", p => new
    {
        InvoicePeriod = p.DateTime(),
        CustomerContractId = p.Long(),
        TypeA = p.String(maxLength: 80),
        TypeB = p.String(maxLength: 80),
        VatValue = p.Decimal(10, 8),
        PurchaseCosts = p.Decimal(10, 8),
        RetailCosts = p.Decimal(10, 8),
    },
    @"begin
        Merge [usagecosts]
        Using (Select
            @InvoicePeriod as invoicePeriod,
            @CustomerContractId as customercontractId,
            @TypeA as typeA,
            @TypeB as typeB,
            @VatValue as vatvalue)
            As tmp 
        On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod
        AND [usagecosts].[customercontractId] = tmp.customercontractid
        AND [usagecosts].[typeA] = tmp.typeA
        AND [usagecosts].[typeB] = tmp.typeB
        AND [usagecosts].[vatvalue] = tmp.Vatvalue)
    When Matched Then 
        Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts, [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts
    When Not Matched Then
        Insert (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)
        Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);
     end;");
}

Remember the Down() method:

    public override void Down()
    {
        this.DropStoredProcedure("dbo.update2");
    }

For completeness: the remote procedure call

using (var dbContext = new DemoContext())
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"InvoicePeriod", usageCosts.InvoicePeriod),
        new SqlParameter(@"CustomerContractId", usageCosts.CustomerContractId),
        new SqlParameter(@"TypeA", usageCosts.TypeA),
        new SqlParameter(@"TypeB", usageCosts.TypeB),
        new SqlParameter(@"VatValue", usageCosts.VatValue),
        new SqlParameter(@"PurchaseCosts", 20M),
        new SqlParameter(@"RetailCosts", 30M),
    };
    string sqlCommand = String.Format(@"Exec {0} @InvoicePeriod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts", functionName);
    dbContext.Database.ExecuteSqlCommand(sqlCommand, functionParameters);
    dbContext.SaveChanges();
}

In my opinion it is best to put this in an extension method of the DbSet. Whenever the UsageCosts changes, the compiler can check for the names and the property types.