如何使用 Entity Framework 1 和 Entity Framework 更新多对多相关对象MySQL？

Question

我有这样的问题。我将 EF1 与 VS2008 SP1 和 VS2008 SP1 一起使用。 MySQL 与 MySQL Connector/Net 6.3.4

我的数据库架构如下所示：

CREATE TABLE IF NOT EXISTS `credential` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `user_credential` (
  `user_id` int(11) NOT NULL,
  `credential_id` int(11) NOT NULL,
  KEY `credential_id` (`credential_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_credential`
  ADD CONSTRAINT `user_credential_ibfk_2` FOREIGN KEY (`credential_id`) REFERENCES    `credential` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_credential_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;

当我尝试执行以下代码时，我遇到异常，我无法理解

var entities = new studyEntities();

var user = new User { Name = "test" };
var credential = new Credential { Name = "admin" };

entities.AddToCredentialSet(credential);
entities.AddToUserSet(user);

entities.SaveChanges();
user.Credentials.Add(credential);
entities.SaveChanges(); // He I have a strange exception thrown


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
  `user_credential`.`credential_id`, 
  `user_credential`.`user_' at line 1

它是什么意思？我怎样才能看到整个查询以找出其中的问题？或者也许我做错了什么？

Answer 1

我有同样的错误，因为我在中间表中没有主键。

这为我解决了这个问题：

CREATE TABLE IF NOT EXISTS `user_credential` (
  `user_id` int(11) NOT NULL,
  `credential_id` int(11) NOT NULL,

  PRIMARY KEY(`user_id`, `credential_id`)
  KEY `user_id` (`user_id`)

  CONSTRAINT .... FOREIGN KEY....
  CONSTRAINT .... FOREIGN KEY....

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 2

看起来像连接器 /NET 中的错误。
我们进行了测试，此代码在 dotConnect fro MySQL 中成功。

Answer 3

向多对多表添加主键。

因此，如果您的表是

CREATE TABLE If Not Exists SubsidiaryMapping
(   
    CompanyId bigint NOT NULL,
    SubsidiaryId bigint NOT NULL,

    -- Add Primary Key composing exisiting keys
    -- Following fixed issue for me
    PRIMARY KEY(CompanyId , SubsidiaryId ), 

    CONSTRAINT .... FOREIGN KEY....
    CONSTRAINT .... FOREIGN KEY....
);