Table 'DBNAME.dbo.TableNAME' doesn't exist Entity Framework 6 with MySQL

Question

I am using Entity Framework 6.0.0 with MySql Server 5.6.17

I have added MySql.Data.Entities through nuget and it installed Entity Framework 6.0.0 and MySql.Data 6.8.4

Everything was setup perfectly and working fine with some of my Business Entities. it have automigration enabled(true).

Later i have added some more entities and then it started giving error like

Table 'DBNAME.dbo.TABLENAME' doesn't exist Entity Framework 6

I have tried deleting whole database and recreate it again, but it didn't worked.

I have tried updating Entity Framework to 6.1.2 and MySql.Data to 6.9.5 but it did not solved problem but gives some other error like

Method not found: 'System.Data.Entity.Migrations.Builders.TableBuilder`1<!0> System.Data.Entity.Migrations.Builders.TableBuilder`1.Index(System.Linq.Expressions.Expression`1<System.Func`2<!0,System.Object>>, System.String, Boolean, Boolean, System.Object)'.

So i changed my EF and MySql.Data to previous version(EF 6.0.0 And MySql.Data 6.8.4)

I found one more article http://bugs.mysql.com/bug.php?id=69649 who has same error like me so i modified my configuration method as below

 public Configuration()
 {
            this.AutomaticMigrationsEnabled = true;
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
            CodeGenerator = new MySql.Data.Entity.MySqlMigrationCodeGenerator();
            AutomaticMigrationDataLossAllowed = true;  // or false in case data loss is not allowed.
 }

but it did not solved issue. i got same error again.

My Sample Business Entity is as below.

public class User
{
    [Key]
    public int UserID { get; set; }
    [Display(Name = "User Email")]
    [AllowHtml]
    public string UserEmail { get; set; }
    [MaxLength(100)]
    [Required(ErrorMessage = "Enter user password")]
    [Display(Name = "User Password")]
    [AllowHtml]
    public string UserPassword { get; set; }
    [MaxLength(50)]
    [Display(Name = "First Name")]
    [AllowHtml]
    public string FirstName { get; set; }
    [MaxLength(50)]
    [Display(Name = "Last Name")]
    [AllowHtml]
    public string LastName { get; set; }
    [Display(Name = "Profile Picture")]
    public string ProfilePicURL { get; set; }
    public string SaltKey { get; set; }
}

Thanks for any help in advance

Your question is tagged "mysql". Yet you are using a three-part naming convention associated with SQL Server. Either use the correct database reference for MySQL or connect to a SQL Server database. — Gordon Linoff
– Gordon Linoff, Commented Jan 20, 2015 at 11:37
@GordonLinoff that is what i am getting in error. i have not specified anything like that AFAIK — Arjun Vachhani
– Arjun Vachhani, Commented Jan 20, 2015 at 11:43

Michał B · Accepted Answer · 2015-10-29 20:03:26Z

13

class SqlGenerator : MySql.Data.Entity.MySqlMigrationSqlGenerator
{
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        IEnumerable < MigrationStatement > res =  base.Generate(migrationOperations, providerManifestToken);
        foreach(MigrationStatement ms in res)
        {
            ms.Sql = ms.Sql.Replace("dbo.","");
        }
        return res;
    }
}
(...)
SetSqlGenerator("MySql.Data.MySqlClient", new SqlGenerator());

Better solution: When i restart application, EF always want to drop and create foreign key constraint one more time, all this strange sql operations contains "dbo." schema. I just ignore them.

foreach(MigrationStatement ms in res)
{
    //ms.Sql = ms.Sql.Replace("dbo.","");
    if(ms.Sql.Contains("dbo."))
    {
        return new List<MigrationStatement>();
    }
}

edited Oct 29, 2015 at 20:03

answered Oct 28, 2015 at 20:09

Michał B

1541 silver badge4 bronze badges

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

2 Comments

maxshuty Over a year ago

Could you add a little more detail to your answer please? Thanks

Arjun Vachhani Over a year ago

@MichalB what you have mentioned is correct. one can find it more at bugs.mysql.com/bug.php?id=69649.

Community · Accepted Answer · 2017-05-23 12:08:44Z

3

The solution given in How can I stop Entity Framework 5 migrations adding dbo. into key names? may work for you. It did for me in several cases.

After 10 months since you asked this question you may have your own approach. Based on my experience I don't recommend using automatic migrations, at least not with MySQL. There are several incompatibilities you need to resolve manually. When using automatic migrations, you can easily break your database and spend hours trying to fix it, if you don't want to loose the data.

The issues include:

incorrect support for column renames
no support for index renames
changes to code-first model are reflected incorrectly in the migrations and you need to tune them

I have solutions to the first 2, if you are interested. The last one is always manual step.

The approach that worked for me 100% so far was to follow the procedure:

Make changes to code-first model
Generate new migration using Add-Migration
Review the generated code and make sure it's correct
Substitute column/index operations with your fixed versions
Apply the migration

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Nov 22, 2015 at 1:53

mlemanczyk

1211 silver badge8 bronze badges

1 Comment

aeroson Over a year ago

In some cases it can be way worse, EF generates migrations that first rename table, then try to rename column in old named table. So far had greatest luck with "update-database -script" and then fine tuning the sql queries as I execute them one by one.

Kmeixner · Accepted Answer · 2015-05-19 18:29:49Z

2

I had the same error, I am using MySQL and CodeFirst. I just deleted the database and restarted the application. This solved the problem for me.

edited May 19, 2015 at 18:29

Kmeixner

1,7724 gold badges22 silver badges35 bronze badges

answered May 19, 2015 at 18:03

Frederico Ribeiro

293 bronze badges

1 Comment

Arjun Vachhani Over a year ago

issue is related to foreign keys. this post solved the issue stackoverflow.com/questions/12053627/…

andypopa · Accepted Answer · 2015-11-10 20:45:20Z

2

This occurs because the table names begin with dbo. in DropIndex or DropForeignKey statements. If you do

Update-Database -Verbose

you'll see the generated SQL & the precise error message:

Can't DROP 'FK_Bunnies_dbo.Carrots_Carrot_CarrotId'; check that column/key exists

the actual FK name being

FK_Bunnies_Carrots_Carrot_CarrotId

It seems that the presence of the dbo. particle only affects DropIndex, DropForeignKey statements (I am running EF + MySQL). CreateIndex, AddForeignKey are not affected by this!

So, the solution is to replace dbo. with nothing in ALL parameters passed to the aforementioned functions.

answered Nov 10, 2015 at 20:45

andypopa

5366 silver badges15 bronze badges

Comments

Tchaps · Accepted Answer · 2016-08-15 06:12:35Z

2

I had this issue today with this code.

DropForeignKey("dbo.BlogPostViews", "BlogPostId", "dbo.BlogPosts");

Removing the dbo.solved my problem. i had this . I changed it to

DropForeignKey("BlogPostViews", "BlogPostId", "BlogPosts");

And everything work ! I got the idea from the @andypopa answer.

answered Aug 15, 2016 at 6:12

Tchaps

8747 silver badges21 bronze badges

Comments

ragerory · Accepted Answer · 2015-05-19 18:54:03Z

0

Is your schema actually dbo? dbo is default in EF. You can change this on the entity by using the Table attribute and providing a new Schema name.

[Table("TableName", Schema = "schemaName")]

answered May 19, 2015 at 18:54

ragerory

1,3761 gold badge10 silver badges29 bronze badges

Comments

Robert Columbia · Accepted Answer · 2017-06-19 22:09:52Z

0

Replace all "dbo." in your migrations to "".

That is, just remove ".dbo"

edited Jun 19, 2017 at 22:09

Robert Columbia

6,45115 gold badges34 silver badges42 bronze badges

answered Jun 19, 2017 at 22:06

rmelobarbosa

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

Table 'DBNAME.dbo.TableNAME' doesn't exist Entity Framework 6 with MySQL

7 Answers 7

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related