Bug #52369 Request for Nested Collection Listing Fetches with Entity Framework
Submitted: 25 Mar 2010 16:16 Modified: 6 Apr 2010 10:02
Reporter: Nick Rellion Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S4 (Feature request)
Version:6.3.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: Collections, entity framework

[25 Mar 2010 16:16] Nick Rellion
Description:
Right now if you try to fetch a table that has a many to many relationship with another table the entity framework will represent that as a collection for the other table.  Trying to expand the collection and the Mysql.Data.Entity class throws an exception of "Specified method is not supported."

		System.Collections.IDictionary {System.Collections.ListDictionaryInternal}

		StackTrace	"   at MySql.Data.Entity.SqlGenerator.Visit(DbApplyExpression expression)\r\n   at System.Data.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)\r\n   at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)\r\n   at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)\r\n   at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.Visit(DbSortExpression expression)\r\n   at System.Data.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)\r\n   at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)\r\n   at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)\r\n   at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)\r\n   at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree)\r\n   at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)\r\n   at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)\r\n   at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)"	string

How to repeat:
CREATE TABLE servers(
	id INT NOT NULL AUTO_INCREMENT,
	server_name CHAR(50),
	PRIMARY KEY(id)
);
CREATE TABLE users(
	id INT NOT NULL AUTO_INCREMENT,
	username CHAR(50),
	PRIMARY KEY(id)
);
CREATE TABLE server_users(
	server_id INT NOT NULL,
	user_id INT NOT NULL,
	PRIMARY KEY(server_id,user_id),
	FOREIGN KEY (server_id) REFERENCES servers(id),
	FOREIGN KEY (user_id) REFERENCES users(id)

);

Load into .net entity framework and force expansion at query time:

var qry = from s in entities.servers select new Server() { server_name=s.server_name, users=s.users };

Loading separately on a per row basis works fine but is not good for remote loading (as each row would have a separate post back to the server), on a table of a few hundred rows and its completely unrealistic .

Suggested fix:
Implement it:) Please.
[6 Apr 2010 10:02] Tonci Grgin
Hi Nick and thanks for your report.

Verified as described.