Access the context inside Up in DbMigration

Question

I'm trying to migrate my db based on my data in my current db. I'm having a hard time querying the db to grab information to allow me to do this. My ideal solution would look something like

public partial class Reset: DbMigration
{
    public override void Up()
    {
        string SqlCmd = "my query";
        if(Execute(SqlCmd) > 5)
        {
            //do this migration
        }
    }
}

So I'm having problem getting data back in the migration.

Edit

Many of you guys have been wondering why I need to do this, so I'll give you guys the nasty details.

So as our project have grown, so did our db migration history. We have over 200 rows in our migration history table. We split the db up and did some maintenance here and there to the point that running all the migrations from scratch with an empty db would result in an error because some tables weren't created yet. Yes its a huge mess and that's why I wanted to clean it up. I ran into this post on SO to reset the migrations to a clean slate. I didn't use the accepted answer because the migration mess we have wouldn't allow it to work. So the second highest answer was used. The only problem with this chosen solution was that for every developer in your team, you'd have to point them to the instructions to update their db or have them download the latest one. They're not able to do

update-database

cleanly. So to allow them to update cleanly and make it hassle free for everyone, I'd like to query the migration history table, see if its the mess and if so delete all the history and skip the actual migration, but still populate the migration row in the table.

'hard time' and 'problem' are very difficult to troubleshoot. What exactly is the problem? an error? — Nick.Mc, May 13 '15 at 03:59
Oh weird, so you want to conditionally perform database migration based on data in the database? Is that common? — jjj, May 13 '15 at 06:08
There's no error just not sure how to query the db tho get a return value inside the migration scripts — Oak, May 13 '15 at 06:10
I don't believe it's common. I'm seeing myself being the first one to run into this hence the question :) — Oak, May 13 '15 at 06:13
I'm curious why you'd want this. If you change the database schema conditionally I'm not sure whether the context will be able to address each schema afterwards and I think that subsequent migrations may be troubled. Maybe you should find a way to hang on to one schema and do the data-dependent parts in business logic or parametrized stored procedures. — Gert Arnold, May 13 '15 at 07:12

score 4 · Accepted Answer · edited May 23 '17 at 12:10

4

If you really want to do what you're looking for, you can execute T-SQL within your migration, for example:

public partial class Reset: DbMigration
{
    public override void Up()
    {
        Sql(@"
            DECLARE @result int
            SELECT @result = <query>

            IF (@result > 5)
            BEGIN
                <migration stuff>
            END");
    }
}

...or some variation of that.

See: DropColumn conditionally in a migration

edited May 23 '17 at 12:10

Community

1
1

answered May 13 '15 at 06:16

jjj

4,822
1
16
39

Is there a way to pull back @result into c#? – Oak May 13 '15 at 17:29
Not that I know of, unfortunately. With this option, I'd probably have the entire migration in the `Sql` call. – jjj May 13 '15 at 21:25
I can't believe EF is meant to be a "code first" method, yet to get data amendments made you have to resort back to T-SQL. – SharpC Sep 27 '22 at 15:41

score 2 · Answer 2 · answered May 13 '15 at 03:57

2

The context can't be created until after the migration is applied, so you can't access it. You could use normal ADO.NET methods to query the database.

What I'd recommend though is putting the code in your Seed method in the database initializer. Seed is run after every migration is applied and is the right place to perform database maintenance when a migration happens. You can just create an empty migration and then in Seed check whether the conditions are right to do whatever you want to do.

It might help if you explained more specifically what you're trying to do.

answered May 13 '15 at 03:57

ChrisV

1,309
9
15

There's a context before this that I'm trying to enter into. The second comment on my question summarized it up pretty well – Oak May 13 '15 at 06:12
Yeah, it's more that I can't imagine an instance when a migration should be applied conditionally. The point of migrations is to keep the database schema in sync with the model. If this isn't done, your application won't work. The possibilities are: 1) you're trying to retrofit migrations into a database that doesn't have them, in which case you should manually sync both the schemas and the data in __MigrationHistory. Or 2) You're trying to do things not related to syncing database schema and model, in which case put it in Seed. – ChrisV May 13 '15 at 06:33
I'd agree with @ChrisV in that migrations generally shouldn't be applied conditionally. If your applications connects to multiple databases, it's likely that the EF model won't be in sync with one of them, and even if the actions within your migration have nothing to do with your model, this would further introduce complexity with future migrations which might have to depend on whether this migration was performed. – jjj May 13 '15 at 06:52
I'm not actually making a new schema. I've added some clarifications on my question to clear things up. Hope it helps and thanks for the answer – Oak May 13 '15 at 17:27

Access the context inside Up in DbMigration

2 Answers2