Entity Framework Code First - MySQL - error can't find table

Question

I'm new to EF, EF Code First, and EF with MySQL. When would EF Code First create your tables within a ASP.NET MVC web project?

I created a Person model. Then generated the Controller and standard Views. When I hit the Index method of the Person controller it tries to pull back a list of all People. Then I get the error:
An error occurred while executing the command definition. See the inner exception for details.
The inner exception:
Table 'testmvc.people' doesn't exist

So I've made it past the connection. But the table wasn't created. How do I create the tables? Also how do I prevent the pluralization of Person to People in the naming scheme?

score 8 · Accepted Answer · edited May 23 '17 at 10:31

The simplest way to generate the database schema (people table and others) is to set a database initializing strategy like this:

Database.SetInitializer<SomeContext>( new 
    DropCreateDatabaseAlways<SomeContext>());

This code needs to run before you attempt to load any data, so the Application_Start() method in Global.asax would be a good place to do that. There are several ways to initialize, so you may want to take a look at them before choosing one, see http://msdn.microsoft.com/en-us/library/system.data.entity%28v=vs.103%29.aspx and look at the methods that implement IDatabaseInitializer. Officially, there is a strategy by default, although I have never quite found that to work for me.

You should also be aware that while this method is great for prototyping and development, you can't quite use it on production database with live data since the database is first dropped and then recreated. There are other methods of doing this at that point - see Database migrations for Entity Framework 4 for possibilities.

Regarding your other question of using non-pluralized table names, there are several ways to do this. One way is to annotate the Person class like this:

[Table("Person")]
class Person
{
    // some field attributes
}

To set this for all tables at once, you can use the fluent API, like this:

class SomeContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {    
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

So is there not a "AlaysUpdate" option? Like one that only knows how to add tables and fields (a non-destructive option)? — BuddyJoe, Jan 13 '12 at 21:12
Unfortunately, as of EF 4.2 there is no baked-in option for a non-destructive update. There is a NuGet package available, called EntityFramework.Migrations that can help you (still in beta), and it is supposed to be included as part of EF 4.3 (which is also currently in beta). Another add on option you can use is RoundhousE - https://github.com/chucknorris/roundhouse, although I'm not sure how well it is maintained. I personally rolled my own migration control system, although I am looking forward to the EF 4.3 release for this feature. — PinnyM, Jan 15 '12 at 03:31

score 2 · Answer 2 · answered Oct 23 '17 at 13:33

MySql with entity framework needs some little tweaks. You need to create three classes(you can check https://learn.microsoft.com/en-us/aspnet/identity/overview/getting-started/aspnet-identity-using-mysql-storage-with-an-entityframework-mysql-provider for more details). First create a MySqlHistoryContext class.

public class MySqlHistoryContext : HistoryContext
{
  public MySqlHistoryContext(
  DbConnection existingConnection,
  string defaultSchema)
  : base(existingConnection, defaultSchema)
{
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);
  modelBuilder.Entity<HistoryRow>().Property(h => 
  h.MigrationId).HasMaxLength(100).IsRequired();
  modelBuilder.Entity<HistoryRow>().Property(h => 
  h.ContextKey).HasMaxLength(200).IsRequired();
}

}

Create a MySqlConfiguration class next

public class MySqlConfiguration : DbConfiguration
{
public MySqlConfiguration()
{
  SetHistoryContext(
  "MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
}

}

Create MySqlInitializer class next

public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>
{
public void InitializeDatabase(ApplicationDbContext context)
{
  if (!context.Database.Exists())
  {
    // if database did not exist before - create it
    context.Database.Create();
  }
  else
  {
    // query to check if MigrationHistory table is present in the database 
    var migrationHistoryTableExists = 
  ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
      "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 
 'IdentityMySQLDatabase' AND table_name = '__MigrationHistory'");

    // if MigrationHistory table is not there (which is the case first time 
  we run) - create it
    if (migrationHistoryTableExists.FirstOrDefault() == 0)
    {
      context.Database.Delete();
      context.Database.Create();
    }
   }
  }
 }

Open the IdentityModels.cs in the model folder.Add this to the ApplicationDbContext : IdentityDbContext class

static ApplicationDbContext()
{
  Database.SetInitializer(new MySqlInitializer());
}

Entity Framework Code First - MySQL - error can't find table

2 Answers2