Description:
Hi,
I came across a problem with the MySQL Connector/NET when querying an Entity that includes a reference to three or more other tables. The other tables do have a foreign key relationship to the original table ("Entity collection").
It seems that an orderby statement is ignored or not applied when we include more than two references from the original Entity to related Entity Collections.
If we define less than three entity collection includings, the problem does not occur and the orderby statement is evaluated correctly. It also seems independent from n:1 relationshops to other tables where the foreign key is included in the main entity.
Database format is InnoDB, MySQL Connector/Net 6.3.6, server version MySQL 5.5.8, Windows based operating system
How to repeat:
Assume four tables within a MySQL database
CREATE TABLE IF NOT EXISTS `maintable` (
`maintable_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`maintable_entry` varchar(255) NOT NULL,
`maintable_date` datetime NOT NULL,
PRIMARY KEY (`maintable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `atable` (
`atable_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`atable_maintable_id` int(10) unsigned NOT NULL,
`atable_name` varchar(255) NOT NULL,
PRIMARY KEY (`atable_id`),
KEY `atable_maintable_id` (`atable_maintable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `btable` (
`btable_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`btable_maintable_id` int(11) unsigned NOT NULL,
`btable_name` varchar(255) NOT NULL,
PRIMARY KEY (`btable_id`),
KEY `btable_maintable_id` (`btable_maintable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `ctable` (
`ctable_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ctable_maintable_id` int(10) unsigned NOT NULL,
`ctable_name` varchar(255) NOT NULL,
PRIMARY KEY (`ctable_id`),
KEY `ctable_maintable_id` (`ctable_maintable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
ALTER TABLE `atable`
ADD CONSTRAINT `atable_ibfk_1` FOREIGN KEY (`atable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE;
ALTER TABLE `btable`
ADD CONSTRAINT `btable_ibfk_1` FOREIGN KEY (`btable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE;
ALTER TABLE `ctable`
ADD CONSTRAINT `ctable_ibfk_1` FOREIGN KEY (`ctable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE;
When I create an entity model and execute the define the following query
var query = (from m in context.MainTableSet.Include("ATable").Include("BTable").Include("CTable")
orderby m.Date
select m).ToList();
the MainTable entries within the list are given back in natural order (as inserted in the database table ordered by ID primaray key)
However, if I remove one of the included satellite tables like
var query = (from m in context.MainTableSet.Include("ATable").Include("BTable")
orderby m.Date
select m).ToList();
the result contains all entries ordered by the date column ascending as expected.
Suggested fix:
It is possible that this description is related to the bug report http://bugs.mysql.com/bug.php?id=59824
A similar topic was discussed here: http://stackoverflow.com/questions/4404571/to-many-entitycollections-in-entity-raise-imple...
We also got the mentioned error "... must implement IConvertible ..." when we used multiple EntityCollections joint together before we upgraded our MySQL Server version to 5.5.8. So far, this error did not occur again but there still seems to be something strange as mentioned above.
I cannot give any other suggestion how to fix this error. I appreciate every hint if you can reproduce this behavior.
Thanks in advance
Andre
Description: Hi, I came across a problem with the MySQL Connector/NET when querying an Entity that includes a reference to three or more other tables. The other tables do have a foreign key relationship to the original table ("Entity collection"). It seems that an orderby statement is ignored or not applied when we include more than two references from the original Entity to related Entity Collections. If we define less than three entity collection includings, the problem does not occur and the orderby statement is evaluated correctly. It also seems independent from n:1 relationshops to other tables where the foreign key is included in the main entity. Database format is InnoDB, MySQL Connector/Net 6.3.6, server version MySQL 5.5.8, Windows based operating system How to repeat: Assume four tables within a MySQL database CREATE TABLE IF NOT EXISTS `maintable` ( `maintable_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `maintable_entry` varchar(255) NOT NULL, `maintable_date` datetime NOT NULL, PRIMARY KEY (`maintable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `atable` ( `atable_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `atable_maintable_id` int(10) unsigned NOT NULL, `atable_name` varchar(255) NOT NULL, PRIMARY KEY (`atable_id`), KEY `atable_maintable_id` (`atable_maintable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `btable` ( `btable_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `btable_maintable_id` int(11) unsigned NOT NULL, `btable_name` varchar(255) NOT NULL, PRIMARY KEY (`btable_id`), KEY `btable_maintable_id` (`btable_maintable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `ctable` ( `ctable_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ctable_maintable_id` int(10) unsigned NOT NULL, `ctable_name` varchar(255) NOT NULL, PRIMARY KEY (`ctable_id`), KEY `ctable_maintable_id` (`ctable_maintable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ALTER TABLE `atable` ADD CONSTRAINT `atable_ibfk_1` FOREIGN KEY (`atable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE; ALTER TABLE `btable` ADD CONSTRAINT `btable_ibfk_1` FOREIGN KEY (`btable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE; ALTER TABLE `ctable` ADD CONSTRAINT `ctable_ibfk_1` FOREIGN KEY (`ctable_maintable_id`) REFERENCES `maintable` (`maintable_id`) ON DELETE CASCADE; When I create an entity model and execute the define the following query var query = (from m in context.MainTableSet.Include("ATable").Include("BTable").Include("CTable") orderby m.Date select m).ToList(); the MainTable entries within the list are given back in natural order (as inserted in the database table ordered by ID primaray key) However, if I remove one of the included satellite tables like var query = (from m in context.MainTableSet.Include("ATable").Include("BTable") orderby m.Date select m).ToList(); the result contains all entries ordered by the date column ascending as expected. Suggested fix: It is possible that this description is related to the bug report http://bugs.mysql.com/bug.php?id=59824 A similar topic was discussed here: http://stackoverflow.com/questions/4404571/to-many-entitycollections-in-entity-raise-imple... We also got the mentioned error "... must implement IConvertible ..." when we used multiple EntityCollections joint together before we upgraded our MySQL Server version to 5.5.8. So far, this error did not occur again but there still seems to be something strange as mentioned above. I cannot give any other suggestion how to fix this error. I appreciate every hint if you can reproduce this behavior. Thanks in advance Andre