MVC3 code-first- EF4.1 doesn't create tables automatically (using MySQL and Connector/Net 6.3.6.)

Question

EF 4.1 together with MySQL and Connector/Net 6.3.6 doesn't create tables auto-magically based on my POCO objects. I'm not sure if I have the configuration wrong or if Connector/.Net 6.3.6 simply doesn't support this functionality. I have crawled the Internet for a week now without finding an answer and I believe I have gone through all the EF and MySQL questions here at stackoverflow. I've noticed many posts refer to dotConnect (e.g. Problems using EF4.1 Code First with MVC3 and MySQL), but I need to find a free solution. I have set up forms authentication, and this works fine with MySQL providers, but I had to create the database schema and tables manually.

I have set up my project in the following way. 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 have created a mock class which 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 'schemeName.shops' doesn't exist

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Table 'schemeName.shops' doesn't exist

Samuel Isola · Accepted Answer · 2011-10-13 02:35:33Z

0

I've found a solution for this problem. You have to get the latest version of Connector/NET 6.4.4. Add the database intializer. Add Persist Security Info=true to your connection string. I did all this and mine is works perfectly.

Also noticed that if you already have an existing database, EF might throw you an EntityDataModel exception. You might have to drop the previous database manually before EF would automatically create both the Database and the tables. From then on any changes you make to your models and DBContext automatically reflect in the Database.

edited Oct 13, 2011 at 2:35

answered Oct 13, 2011 at 1:11

Samuel Isola

6001 gold badge5 silver badges14 bronze badges

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

2 Comments

tyron Over a year ago

I'm having some problems using EF4.1 + MySQL too. Have you tried any context envolving Inheritance in your POCO classes? Let me suggest you refactor your class "ProductModel"; turn it into an abstract class, create a class "ProductWithImageModel", which inherits "ProductModel" and a "ProductWithoutImageModel", which also inherits from "ProductModel". In the first one, put your "ImageUrl" property; in the last, you can't leave empty (its just our control). Can you manage to make it work?

tyron Over a year ago

this is where I describe my problem with more details stackoverflow.com/q/8700497/891772

37Stars · Accepted Answer · 2011-06-28 04:45:25Z

0

Have you tried this using SQL Express to see if you get the same error?

Have you tried creating a database initializer? The class would be declared like the code below. Have it seed some table with values.

public class MySQLConnInitializer : DropCreateDatabaseIfModelChanges<MySQLConn>

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

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

answered Jun 28, 2011 at 4:45

37Stars

2,49920 silver badges24 bronze badges

2 Comments

Howie Over a year ago

I have tried with SQL Express locally and the tables with columns are created correctly. I gave up the code-first approach. Now I'm creating my tables manually and updating the EF model for each change in the db. This works, so I guess I'll stick to it.

37Stars Over a year ago

I am now experiencing the same problem. At first my project was dropping and recreating the database. Then I had to manually drop the database. Now it doesn't do anything unless I tell it to do so. Try adding the line 'Database.CreateIfNotExists();' to your MySQLConn constructor.

Fernando Gonzalez Sanchez · Accepted Answer · 2014-02-17 22:48:10Z

0

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

answered Feb 17, 2014 at 22:48

Fernando Gonzalez Sanchez

3,5356 gold badges36 silver badges47 bronze badges

Collectives™ on Stack Overflow

MVC3 code-first- EF4.1 doesn't create tables automatically (using MySQL and Connector/Net 6.3.6.)

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related