Generate stored procedures automatically in Entity Framework code-first

Question

I have a set of classes A[1..n] that all inherit from an abstract class B. Each of these A instances would only have a single property added - a foreign key mapping to a separate table for each instance. Each foreign key column has the name ForeignKeyId, not the name of the table, so essentially I have a set of tables that are pretty much identical except for the name.

I want to write a stored procedure that loads records from these tables using some specific SQL grouping and ordering and joining and whatnot. The important part isn't the query itself, but creating the stored procedure with a specific name automatically without adding it line by line manually in the migration itself (which is the top-rated answer in this question: Create Stored Procedures using Entity Framework Code First?).

I've tried creating additional operations in the MigrationCodeGenerator:

public override ScaffoldedMigration Generate( string migrationId, 
            IEnumerable<MigrationOperation> operations, string sourceModel, 
            string targetModel, string @namespace, string className )
{
    IList<MigrationOperation> operationsList = operations as IList<MigrationOperation> ?? operations.ToList();            

    var drop = new DropProcedureOperation($"{className}_LoadVersion");
    var create = new CreateProcedureOperation($"{className}_LoadVersion", $"select * from {className}");

    operationsList.Add( drop );
    operationsList.Add(create);

    CSharpMigrationCodeGenerator generator = new CSharpMigrationCodeGenerator();

    return generator.Generate( migrationId, operationsList, sourceModel, targetModel, @namespace, className );
}

However, my migration is completely empty:

public partial class TestMigration : DbMigration
{
    public override void Up()
    {
    }

    public override void Down()
    {
    }
}

Once I can get the generator to start creating anything, I can fine-tune it from there, but I'm stumped on how to get it to create a stored procedure on a migration.

Answer 1

So after some additional digging, I was able to figure out how to accomplish what I needed.

All of my stored procedures are heavily based off the table structure, so I can check what kind of change is occurring during a migration and modify the proc as appropriate. The ScaffoldMigration function contains the list of operations for a given migration, each of type MigrationOperation. This class has numerous sub-classes found here: https://msdn.microsoft.com/en-us/library/system.data.entity.migrations.model.migrationoperation(v=vs.113).aspx

As the stored procedures need, at the very least, the table names to build the stored procedures (as I need one stored proc per table), I need to check if the operation is of the right type, and if so, pull the correct value out of it:

foreach (MigrationOperation operation in operationsList)
{
    if (operation is CreateTableOperation)
    {
        CreateTableOperation op = (CreateTableOperation) operation;
        if (op.Name.Contains( ChangeTracker ))
                procOps.Add(CreateLoadVersionProc(op.Name));            
    }
}

Where procOps is just a list of Migration Operations: List<MigrationOperation> procOps = new List<MigrationOperation>();.

The CreateLoadVersionProc simply returns a new CreateProcedureOperation object:

private static CreateProcedureOperation CreateLoadVersionProc(string tableName)
{
    string proc = $@"select x.*
        from {tableName} x
        where x.CreatedTimeStamp >= @target";

    var op = new CreateProcedureOperation($"{tableName}_LoadVersion", proc);
    var dateParam = new ParameterModel(PrimitiveTypeKind.DateTime) {Name = "@target"};
    op.Parameters.Add(dateParam);           

    return op;
}

Then, back in my ScaffoldMigration function, I simply add the list of stored procedure operations I created to the list of operations passed into the function:

if (procOps.Any())
    operationsList.AddRange(procOps);

Then the function proceeds to the CSharpMigrationGenerator creation and the Generate function call to spit out a migration.