Bug #61149 OrderBy ignored in EntityDataSource with multiple included Entity Collections
Submitted: 12 May 2011 10:59 Modified: 3 Jun 2011 15:11
Reporter: Andre Langer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.6 OS:Microsoft Windows (MySQL 5.5.8)
Assigned to: Julio Casal CPU Architecture:Any
Tags: C#, Collection, connector, entity framework, linq, NET, orderby

[12 May 2011 10:59] Andre Langer
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
[17 May 2011 11:40] Richard Deeming
Have you looked at the generated SQL for your commands?

If you add the "Logging=true" parameter to your connection string, you can configure the connector to dump the SQL to the debug window:

http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-tracing.html
http://dev.mysql.com/doc/refman/5.5/en/connector-net-connection-options.html
[3 Jun 2011 15:11] Julio Casal
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed versions: 6.1.6, 6.2.5, 6.3.7, 6.4.1+.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html