Geek Answers Handbook

MVC3 code-first-EF4.1 does not automatically create tables (using MySQL and Connector / Net 6.3.6.)

EF 4.1, together with MySQL and Connector / Net 6.3.6, does not create tables automatically magically based on my POCO objects. I'm not sure if I have the wrong configuration or if Connector / .Net 6.3.6 just doesn't support this feature. I scanned the Internet for a week without finding an answer, and I believe that I examined all the EF and MySQL issues here in stackoverflow. I noticed that many posts reference dotConnect (e.g. Problems using EF4.1 code with MVC3 and MySQL ), but I need to find a free solution. I set up forms authentication and it works fine with MySQL providers, but I had to manually create the database schema and tables.

I created my project as follows. Parts of my web.config file.

<connectionStrings> <add name="MySQLConn" connectionString="Server=localhost;Database=XxX;Uid=xXx;Pwd=xXx;" providerName="MySql.Data.MySqlClient" /> </connectionStrings> 

  <profile defaultProvider="MySqlProfileProvider" enabled="true"> <providers> <clear /> <add name="MySqlProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider,MySql.Web,Version=6.3.6.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d" connectionStringName="MySQLConn" applicationName="/" /> </providers> </profile> <roleManager enabled="true" defaultProvider="MySqlRoleProvider"> <providers> <clear /> <add name="MySqlRoleProvider" type="MySql.Web.Security.MySQLRoleProvider,MySql.Web,Version=6.3.6.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d" connectionStringName="MySQLConn" applicationName="/" /> </providers> 

 <system.data> <DbProviderFactories> <clear /> <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /> </DbProviderFactories> </system.data>

My POCO objects:

 [XmlRoot(ElementName = "Shop", IsNullable = false)] [Table("shops")] public class ShopModel { [Key] public int Id { get; set; } // must be unique [Required] public string Name { get; set; } public string SiteUrl { get; set; } public string LogoUrl { get; set; } public string AffiliateSuffix { get; set; } public virtual ICollection<ProductModel> ProductModels { get; set; } } [XmlRoot(ElementName = "Product", IsNullable = false)] [Table("products")] public class ProductModel { [Key] public int Id { get; set; } [Required] public string Title { get; set; } [Required] public decimal Price { get; set; } public string ImageUrl { get; set; } public string ProductUrl { get; set; } [Required] public virtual ShopModel ShopModel { get; set; } }

My DBContext

  public class MySQLConn : DbContext { public DbSet<ShopModel> Shops { get; set; } public DbSet<ProductModel> Products { get; set; } }

I created a mock class that creates a new Shop and a new Product object, like this ctx.Shops.Add (new ShopModel {...}); ctx.Savechanges (); // code stops here

When I run my code, I get the following error:

Table 'schemName.shops' does not exist

Description: An unhandled exception occurred during the execution of the current web request. View the stack trace for more information about the error and its occurrence in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: The table "schemName.shops" does not exist

+4
source share
3 answers

I found a solution to this problem. You should get the latest version of Connector / NET 6.4.4. Add a basic analyzer. Add Persist Security Info = true to the connection string. I did all this and mine works fine.

Also noticed that if you already have an existing database, EF may throw you an EntityDataModel exception. You may need to manually abandon the previous database before EF automatically creates the database and tables. Since then, any changes you make to your models and DBContext are automatically reflected in the database.

0
source

Have you tried this with SQL Express to find out if you have the same error?

Have you tried creating a database initializer? The class will be declared as the code below. Ask him to sow some table with values.

 public class MySQLConnInitializer : DropCreateDatabaseIfModelChanges<MySQLConn>

Then in Global.asax.cs you would add the following line:

 Database.SetInitializer<MySQLConn>(new MySQLConnInitializer ());
0
source

MySql Connector / NET does not support Entity Framework Code First up to version 6.6 (Currently, any of 6.6.6, 6.7.4 or 6.8.3 will do).

0
source

More articles:


All Articles