实体框架(EF6)+ MySql数据库优先模型多对多关系错误查询生成 [英] Entity Framework (EF6) + MySql Database First Model Many to Many Relationship Wrong Query Generation

问题描述

我有一个项目，该项目具有现有的数据库结构和数据.当前项目正在PHP + MySQL上运行，我正尝试从PHP切换到C#EF 6.0 + MySQL数据库第一种方法.

I have a project which has an existing database structure and data. Currently project is running on PHP + MySQL and I'm trying to switch from PHP to C# EF 6.0 + MySQL Database first approach.

我已经安装了NuGet软件包(EF 6.0和MySQL.Data，MySQL.Web，MySQL.Data.Entity，MySQL.Data.Entities)，并根据使用MySQL数据提供程序修改了我的app.config.到此状态，一切都可以正常运行，我可以从数据库生成模型，并且可以在每个表上插入数据而没有多对多关系表的问题.我创建了2个虚拟表，名称分别为TableA和TableB以及关系表TableA_TableB，以请求有关我的问题的帮助.

I have installed NuGet Packages (EF 6.0 and MySQL.Data, MySQL.Web, MySQL.Data.Entity, MySQL.Data.Entities) and modified my app.config according to use MySQL data provider. Up to this state everything works perfectly, I can generate model from database and can insert data on each table without any issue but many-to-many relation tables. I have created 2 dummy tables with name TableA and TableB and the relation table TableA_TableB for requesting help about my issue.

您可以在下面看到模型生成的屏幕截图:

You can see the screen shots of the model generation below:

以及下面生成的模型:

And the generated model below:

到目前为止，一切看起来都不错，但是正如我在尝试在TableA和TableB之间插入关系时提到的那样，它失败了.您可以在下面看到我的C#代码:

Up to here everything looks fine but as I mentioned when I try to insert a relation between TableA and TableB it fails. You can see my C# code below:

public static void Create()
{
    using (MainDataContext mainDataContext = new MainDataContext())
    {
        try {
            mainDataContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
            TableA tableA = new TableA();
            tableA.Title = "My Title";
            tableA.Description = "MyDescription";
            mainDataContext.TableA.Add(tableA);
            mainDataContext.SaveChanges();

            TableB tableB = new TableB();
            tableB.Title = "Table B Title";
            tableB.Description = "Table B Description";
            mainDataContext.TableB.Add(tableB);
            mainDataContext.SaveChanges();

            tableA.TableB.Add(tableB);
            mainDataContext.SaveChanges();
        }
        catch (Exception ex)
        {

        }
    }
}

mainDataContext.SaveChanges()的前两个；工作正常，但最后一个mainDataContext.SaveChanges();抛出关于表FK的异常(我也尝试过失败的单个事务)，但是在挖掘调试输出后，我遇到了异常的真正原因.我在数据上下文中打开了调试输出，在这里您可以看到EF的insert语句的输出，您可以在下面看到它:

The first two of the mainDataContext.SaveChanges(); works fine but the last mainDataContext.SaveChanges(); throws exception (I tried the single transaction it fails too) about FK of my table but after I dig the debug output I faced the exception's real reason. I turn on debug output within my data context and here you can see the output of the insert statements of the EF you can see it below:

在2016年1月1日23:30:46 +02:00打开连接 在2016年1月1日23:30:46 +02:00开始交易

Opened connection at 11.1.2016 23:30:46 +02:00 Started transaction at 11.1.2016 23:30:46 +02:00

SET SESSION sql_mode ='ANSI';插入到TableA( Title， Description)值( @ gp1， @ gp2); 选择 ID 从TableA 在哪里row_count()> 0 AND ID = last_insert_id() -@ gp1:'我的标题'(Type = String，IsNullable = false，Size = 8) -@ gp2:'MyDescription'(类型=字符串，IsNullable =否，大小= 13) -在11.1.2016 23:30:47 +02:00执行 -在1毫秒内完成，结果为:EFMySqlDataReader

SET SESSION sql_mode='ANSI';INSERT INTO TableA( Title, Description) VALUES ( @gp1, @gp2); SELECT ID FROM TableA WHERE row_count() > 0 AND ID=last_insert_id() -- @gp1: 'My Title' (Type = String, IsNullable = false, Size = 8) -- @gp2: 'MyDescription' (Type = String, IsNullable = false, Size = 13) -- Executing at 11.1.2016 23:30:47 +02:00 -- Completed in 1 ms with result: EFMySqlDataReader

在11.1.2016 23:30:47 +02:00提交的交易 在11.1.2016 23:30:47 +02:00关闭连接 在11.1.2016 23:30:47 +02:00处理的交易 在11.1.2016 23:30:55 +02:00打开连接 在2016年1月1日开始交易23:30:55 +02:00

Committed transaction at 11.1.2016 23:30:47 +02:00 Closed connection at 11.1.2016 23:30:47 +02:00 Disposed transaction at 11.1.2016 23:30:47 +02:00 Opened connection at 11.1.2016 23:30:55 +02:00 Started transaction at 11.1.2016 23:30:55 +02:00

SET SESSION sql_mode ='ANSI';插入TableB( Title， Description)值( @ gp1， @ gp2); 选择 ID 从TableB 其中row_count()> 0 AND ID = last_insert_id()

SET SESSION sql_mode='ANSI';INSERT INTO TableB( Title, Description) VALUES ( @gp1, @gp2); SELECT ID FROM TableB WHERE row_count() > 0 AND ID=last_insert_id()

-@ gp1:'表B标题'(类型=字符串，IsNullable =假，大小= 13) -@ gp2:表B说明"(类型=字符串，IsNullable =否，大小= 19) -在11.1.2016 23:30:55 +02:00执行 -在6毫秒内完成，结果为:EFMySqlDataReader

-- @gp1: 'Table B Title' (Type = String, IsNullable = false, Size = 13) -- @gp2: 'Table B Description' (Type = String, IsNullable = false, Size = 19) -- Executing at 11.1.2016 23:30:55 +02:00 -- Completed in 6 ms with result: EFMySqlDataReader

在11.1.2016 23:30:55 +02:00提交的交易 在11.1.2016 23:30:55 +02:00关闭连接 在11.1.2016 23:30:55 +02:00处理的交易 在11.1.2016 23:30:58 +02:00打开连接 在2016年1月1日23:30:58 +02:00开始交易

Committed transaction at 11.1.2016 23:30:55 +02:00 Closed connection at 11.1.2016 23:30:55 +02:00 Disposed transaction at 11.1.2016 23:30:55 +02:00 Opened connection at 11.1.2016 23:30:58 +02:00 Started transaction at 11.1.2016 23:30:58 +02:00

插入(选择 TableA_TableB.TableAID， TableA_TableB.TableBID 从TableA_TableB AS TableA_TableB)( TableAID， TableBID)值( 1， 1)

INSERT INTO (SELECT TableA_TableB.TableAID, TableA_TableB.TableBID FROM TableA_TableB AS TableA_TableB)( TableAID, TableBID) VALUES ( 1, 1)

-在2016年1月1日执行23:30:58 +02:00

-- Executing at 11.1.2016 23:30:58 +02:00

-在3毫秒内失败，并出现错误:您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取在'(SELECT TableA_TableB.TableAID， TableA_TableB.TableBID 从第1行的"TableA_Tab"

-- Failed in 3 ms with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT TableA_TableB.TableAID, TableA_TableB.TableBID FROM `TableA_Tab' at line 1

在11.1.2016 23:30:58 +02:00关闭连接 在11.1.2016 23:30:58 +02:00处理的交易 抛出异常:EntityFramework.dll中的'System.Data.Entity.Infrastructure.DbUpdateException'

Closed connection at 11.1.2016 23:30:58 +02:00 Disposed transaction at 11.1.2016 23:30:58 +02:00 Exception thrown: 'System.Data.Entity.Infrastructure.DbUpdateException' in EntityFramework.dll

我的桌子的DDL也在下面:

Also my table's DDLs below:

CREATE TABLE `TableA` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `TableB` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `TableA_TableB` (
  `TableAID` int(10) unsigned NOT NULL,
  `TableBID` int(10) unsigned NOT NULL,
  KEY `TableA_TableB_TableAID` (`TableAID`),
  KEY `TableA_TableB_TableBID` (`TableBID`),
  CONSTRAINT `TableA_TableB_TableAID` FOREIGN KEY (`TableAID`) REFERENCES `TableA` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TableA_TableB_TableBID` FOREIGN KEY (`TableBID`) REFERENCES `TableB` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我想知道这是一个错误还是我做错了任何人对我的问题有任何想法吗?

I wonder if this is a bug or I'm doing something wrong does anyone have any idea for my issue ?

提前谢谢！

推荐答案

问题出在添加解决问题的PK(TableAID，TableBID)之后，我的关系表TableA_TableB缺少PK.此外，在EF上使用uint会导致单个保存问题(您可以一张一张地保存表，获得ID后可以创建关系)

The problem was about the missing PK on my relation table TableA_TableB after adding PK (TableAID,TableBID) the problem solved. Additionally using uint on EF causes single save issue (you can save tables one by one and after getting ID's you can create relations)

这篇关于实体框架(EF6)+ MySql数据库优先模型多对多关系错误查询生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！