Entity Framework on a SQL instance

Question

Every EF tutorial I've looked at requires LocalDB or SQL Express to be used in a code-first approach with EF.

Is there a way to connect to a traditional SQL DB (2014) instance using code-first EF and have EF create the schema in the DB? Or would I have to connect to a DB with an exsiting matching schema when switching to production?

It's fine to use LocalDB for dev purposes, but when deploying to production, I'd like to plug into an actual and existing SQL DB. Is there a way to do this without plugging into a DB that has had its schema manually made to match the EF models?

There is no difference between express and a normal install, Have you tried setting the connection string to a real database and seeing what happens? — Scott Chamberlain
– Scott Chamberlain, Commented Jul 21, 2014 at 21:49
I've tried to connect to my localhost instance of a SQL DB and it works fine, but if I update a model, it breaks because the DB schema is not auto-updated by EF (I have to go to Mgmt Studio and tweak the schema to match the EF models). — Jesse
– Jesse, Commented Jul 21, 2014 at 21:51
Please show some code showing how you set your Initialization strategy. (The place where you have Seed()) — Scott Chamberlain
– Scott Chamberlain, Commented Jul 21, 2014 at 21:53
@DavidG, I am not using LocalDb (trying to get this to work with a regular SQL DB instance on localhost), but I believe it creates the LocalDb schema from the model classes. @ScottChamberlain, I am not calling Seed() anywhere, I'm just creating a test object and inserting it, then calling dbContext.SaveChanges(). If the object has a field that is not in the DB schema, it throws and exception instead of dropping the table/db and auto-creating the schema to match the model classes. — Jesse
– Jesse, Commented Jul 21, 2014 at 21:58

woot · Accepted Answer · 2014-07-22 01:03:41Z

I would try using Entity Framework's Code First Migrations (http://msdn.microsoft.com/en-us/data/jj591621.aspx).

Short version:

1) In your DbContext, set the initializer to CreateDatabaseIfNotExists similar to Database.SetInitializer<YourDbContext>(new CreateDatabaseIfNotExists<YourDbContext>());

This will allow EF to create the DB if no database exists (the first time the application is run).

2) Next, go to the Package Manager Console, select your Database project (if separate), and enter the command Enable-Migrations. This will create some migration scaffolding and an initial migration.

Whenever you change your database model in code from then on, do the following steps:

3) After you've changed your code-first model, go to the Package Manager Console, select the Database project, and type the command Add-Migration MigrationNameHere This will scaffold out a new separate migration that can be applied to your database. Note, this command will run for whatever db is in your current connection string config.

4) To apply the migration, and update your database to the new schema, type the command Update-Database. If everything goes well, your database will be updated (with data) to the new schema! You should now be able to run your MVC project without db errors.

Keep in mind, this is the very basic version of db-migrations, please try it out on a dummy project first, and of course backup your data. If the changes are complex, or you need to do a special migration of data, you can edit the scaffolded migration code in the Migrations folder created, before doing the Update-Database.

Hope this helps!

Collectives™ on Stack Overflow

Entity Framework on a SQL instance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related