CODEWITHSUNDEEP
c#mysqlentity-framework
Deadman
Deadman

Reputation: 11

MySQL 8.0.22 + EntityFramework - Table doesn't exist (database/schema twice)

I'm using EntityFramework for my ASP.Net website and used MsSQL before which worked fine. Now I'm trying to migrate from MsSQL to MySQL (database got migrated from MySQL to MsSQL using the Migration Wizard of MySQL Workbench 8.0.22). This is my setup:

I'm able to create the entities with EntityFrameWork from my MySQL database/schema "test". But as soon as I try to work with the entities, I get the following error:

Table 'test.test.tbl_groupspermissions' doesn't exist

I guess the problem is, that it tries to access the table "tbl_groupspermissions" with the database/schema name twice in it (test.test).

I've already checked if the name of the table is correct.

My web.config:

    <entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework, Version=8.0.22.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </providers>
</entityFramework>
<connectionStrings>
  <add name="testConnectionString" connectionString="server=localhost;port=3306;database=test;user id=root;password=******;" providerName="MySql.Data.MySqlClient" />
  <add name="testMySQLEntities" connectionString="metadata=res://*/Entities.MySQLModel.csdl|res://*/Entities.MySQLModel.ssdl|res://*/Entities.MySQLModel.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=localhost;port=3306;user id=root;password=******;database=test;persistsecurityinfo=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

The error appears on this part (testEntities.tbl_groupsstatus.Where):

    public bool hasGuestStatus(int statusId)
{
    List<tbl_groupsstatus> statis = testEntities.tbl_groupsstatus.Where(p => p.groupId == (int)ProtectedGroups.Gast).ToList();
    bool hasStatus = false;
    foreach (tbl_groupsstatus status in statis)
    {
        if (status.statusId == statusId) { hasStatus = true; }
    }
    return hasStatus;
}

The stacktrace tells me, that its using the correct driver:

    [MySqlException (0x80004005): Table 'test.test.tbl_groupspermissions' doesn't exist]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +309
   MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +67
   MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +17
   MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +110
   MySql.Data.MySqlClient.MySqlDataReader.NextResult() +931
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1636
   MySql.Data.EntityFramework.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +33

I've seen related problems with version 8.0.22 here, but without any satisfying solution for me:

Table 'dbname.dbname.tablename' doesn't exists error in Visual Studio when adding Mysql Entity Framework

VS 2019 and MySQL Entity Framework duplicate database name

Schema/DB Name added 2 times in EF for MYSQL DB

Entity framework migration "Table does not exist"

Thanks for any help.

UPDATE As Bradley Grainger mentioned, its propably a bug of the component MySql.Data.EntityFramework 8.0.22. Downgrade to MySql.Data.EntityFramework 8.0.21 solved the problem for me.

Upvotes: 1

Views: 2946

Answers (4)

dognose
dognose

Reputation: 20889

I've had this issue with 3 tables, while all the other were fine...

Checked the edmx File generated (DB-First-Approach), and their entries were different, for whatever reason:

Working rows:

<EntitySet Name="Unit" EntityType="Self.Unit" Schema="db_test" store:Type="Tables" />
<EntitySet Name="User" EntityType="Self.User" Schema="db_test" store:Type="Tables" />
<EntitySet Name="User_to_Role" EntityType="Self.User_to_Role" Schema="db_test" store:Type="Tables" />

And the tables causing the issues where defined as:

<EntitySet Name="TaggedSearchResult" EntityType="Self.TaggedSearchResult" store:Type="Tables" store:Schema="db_test">
            <DefiningQuery>SELECT
`TaggedSearchResult`.`MatchPrimaryKeyValue`, 
`TaggedSearchResult`.`PredicatePrimaryKeyValue`, 
`TaggedSearchResult`.`PredicateEntityName`, 
`TaggedSearchResult`.`PredicateEntityColumn`, 
`TaggedSearchResult`.`PredicateKeyWord`
FROM `db_test.TaggedSearchResult` AS `TaggedSearchResult`</DefiningQuery>
          </EntitySet>

I have no idea, why these 3 tables cause a different markup - but just modifying it to look like the first representation and everything works as expected.

Upvotes: -1

kevinfma
kevinfma

Reputation: 21

I encountered this same issue with this setup:
-MySQL Server 8.0.25
-MySQL for Visual Studio 8.0.25 -Connector/NET 8.0.25
-NuGet: EntityFramework 6.4.4
-NuGet: MySql.Data 8.0.25
-NuGet: MySql.Data.EntityFramework 8.0.25

The solution for me was reinstalling the MySQL for Visual Studio extension and modifying the MySQLModel.edmx file like in @adamskorp answer. Maybe it was a bug on the installation or something like that.

Upvotes: 0

adamskorp
adamskorp

Reputation: 81

  1. Close your visual studio project
  2. Edit MySQLModel.edmx file with notepad
  3. Replace matches in file, from Schema="Test" to Schema=""
  4. Save MySQLModel.edmx file
  5. Open visual studio project
  6. Compile Project
  7. Run Application

Upvotes: 8

Bradley Grainger
Bradley Grainger

Reputation: 28162

This is a known bug in MySql.Data.EntityFramework 8.0.22: Bug 101236.

You may be able to work around it by downgrading MySql.Data.EntityFramework to 8.0.21.

Upvotes: 1

Related Questions