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

Answer 1

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.