How to update many-to-many related objects with Entity framework 1 & MySQL?

Question

Hello,

I have such a problem. I'm using EF1 with VS2008 SP1 & MySQL with MySQL Connector/Net 6.3.4

My database schema looks like this:

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;

While I'm trying to execute the folowing code I have exception that I cannot understand

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

What does it mean? How can I see the whole query to seek the problem in it? or maybe what I'm doing wrong?

Answer 1

Looks like a bug in Connector /NET.
We have made a test, this code succeeded in dotConnect fro MySQL.