Execute Raw SQL on Entity Framework Migration

Question

I need to execute raw SQL as part of my database initialization with Entity Framework. The SQL is required to set the collation of a column and when given a context the code looks something like this

context.Database.ExecuteSqlCommand("ALTER TABLE my_table ALTER COLUMN my_column nvarchar(200) COLLATE Latin1_General_CS_AS");

The question is where to put this code? If I put it in the Seed method of Configuration.cs created by EF when I enable-migrations it'll work but I'll lose all knowledge of performing the SQL if I decide to delete the migrations and rebaseline at a later date. Ideally I want to abstract this SQL execution to another class that EF knows to execute each time I enable migrations and update the database.

I thought about using a database initializer but on further reading it appears that these are only invoked by the calling application: this shouldn't be the responsibility of the calling application - I want it to set the collation when entity framework creates the database.

How can I ensure that I execute raw SQL as part of my EF initial/baseline configuration such that I won't lose knowledge of what was done in the future (i.e. not just lumping it all into the seed method of the default Configuration.cs)

Leotsarev · Accepted Answer · 2017-07-25 14:15:42Z

4

void Up() {
   Sql("ALTER TABLE my_table ALTER COLUMN my_column nvarchar(200) COLLATE Latin1_General_CS_AS");
}

Another option is to do that as part of Seed method, but migrations seems more sane.

answered Jul 25, 2017 at 14:15

Leotsarev

1,0829 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James B Over a year ago

Sure, but is embedding this a migration the only way? When building the database for the first time I often find myself clearing out the migrations and starting fresh otherwise I end up with a load of migrations that are really just noise as the model develops. I'd have to remember to add these sql statement each time.

Shroombaker Over a year ago

Did you find another solution for this yet, @JamesB?

Collectives™ on Stack Overflow

Execute Raw SQL on Entity Framework Migration

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related