2

I'm trying to use the code-first approach with a MySQL db.

The kicker here, is that I can't drop and create the DB (on my hosting company) so I can't automagically create the tables in the database. I've looked at http://nuget.org/List/Packages/EFCodeFirst.CreateTablesOnly and Entity Framework CTP 4 - Code First Custom Database Initializer but I can't seem to get that to work with my MySQL.

Edit: Basically I'm looking for a solution that works like the above - by ONLY dropping and creating tables, and not the database itself, with MySQL.

Has anyone had any luck with this approach?

Thanks

Community
  • 1
  • 1
Dynde
  • 2,592
  • 4
  • 33
  • 56

2 Answers2

1

This isn't what you asked for directly, but it might give you similar results.

You might try creating the database from scratch with your code-first approach, then take a diff between your fresh DB and the production DB to create migration scripts. These migration scripts could be executed in production without dropping your tables.

I know this ends up being a low-tech approach, but it could work for your scenario. The migration script approach is exactly how a lot of real companies do DB migrations/promotions to more restricted environments.

If you have a good idea of the types of changes you're going to make to your DB, then you could look for a code-first-ish solution for migration scripts that isn't EF specific. Something like Sharp Migrations, or one of many similar tools.

Community
  • 1
  • 1
Merlyn Morgan-Graham
  • 58,163
  • 16
  • 128
  • 183
  • Thanks for the input, although I think, if I were going with a low-tech approach, I'd setup a local mysql, with permissions to drop and create databases, and then simply export the database to the hosted mysql :) – Dynde Nov 28 '11 at 12:44
  • @Dynde: Same diff, though some environments are more friendly to stuff like that, others are more friendly to you handing off DB scripts to DBAs. Just depends on your company. Did you read the last bit about C# migration scripts? Many people like them, and that is a bit less "low tech". – Merlyn Morgan-Graham Nov 28 '11 at 19:36
  • You're right. Since this is a small private project though, I'll probably end up with the low-tech approach. The convenience of EF automatically creating the tables (without dropping the DB) would just be much less hassle - especially since it seems to work with SQL Server. I'll look into the migration tools though - thanks, marking as answer, since it's closest to (without actually having EF autogenerating) – Dynde Nov 29 '11 at 12:30
  • 1
    @Dynde: I appreciate an upvote but I personally would like to see more answers. If you wish to unaccept with hopes of getting more answers, you have my blessing :) But definitely check this out as a work-around. – Merlyn Morgan-Graham Nov 29 '11 at 19:35
0

I am using code first with an existing database. All you need to do is set your connectionstring in the web.config like the following.

NOTE:- Yours might vary because u use MySQL.

<connectionStrings>
<add name="PriceCompareEntity"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\PriceCompare.mdf;User Instance=true"
     providerName="System.Data.SqlClient" />
</connectionStrings>

Then just make sure that your model classes matches the tables in database. Database aren't dropped and created on fly, unless its told too, like in the following case i am asking the EF to create the database in Global.asax

System.Data.Entity.Database.SetInitializer(new MvcMusicStore.Models.SampleData());

For more information, you can visit EF Code first with Existing DB

Pankaj Upadhyay
  • 12,966
  • 24
  • 73
  • 104
  • I'm sorry, I was a little vague. I'm having no problem connection to the database. What I'm looking for is a way to just generate the tables, and not the Database - just like the solutions I linked in my questions but with MySQL – Dynde Nov 28 '11 at 10:45
  • I am not sure that can be done. The entire database is generated with the Code-first. I don't think there is a way to add tables to existing database using code-first. – Pankaj Upadhyay Nov 28 '11 at 10:48
  • Well, the links I provided seem to work for SQL server - so obviously it's possible. All I need is for it to work with MySQL. – Dynde Nov 28 '11 at 10:49