strangeness with entity framework

I have a database (mysql) for an account management system that contains a user table

the user table references two other important tables:

site
user_group

user_group references two tables:

user_group_user_activity
user_group_trans_type

user_group_user_activity references user_activity
user_group_trans_type references trans_type

I have an entity data model created for this database using the data connector from mysql (version 6.2.2).

what I'm trying to do is this:

Code:

user u = dbObject.users.Include("sites")
                       .Include("user_groups")
                       .Include("user_groups.user_group_user_activities")
                       .Include("user_groups.user_group_trans_types")
                       .Include("user_groups.user_group_user_activities.user_activity")
                       .Include("user_groups.user_group_trans_types.trans_type")
                       .FirstOrDefault();

I am getting the following exception at runtime:

Code:

[System.Data.EntityCommandCompilationException] = {"An error occurred while preparing the command definition. See the inner exception for details."}

and the inner exception:

Code:

InnerException = {"Specified method is not supported."}

so it seems like it's having trouble constructing a query for this request.

am I requesting objects too deep in the tree for the connector to handle, or is there something else I'm doing wrong?

if I only request user_group_user_activities or user_group_trans_types it works fine, but when I add the other, it fails.

if I omit the includes and explicitly call user_group_trans_types.Load() and user_group_user_activities.Load() on each of the user_groups, it loads them, but omits the user_activity and trans_type members. is there a way to make it load them explicitly?

Sorry, no experience with MySQL in C#, only MSSQL. What does .Include() do? Better yet, what data does a 'user' object have after that chain of extension methods is called?

Originally Posted by StainedBlue

Sorry, no experience with MySQL in C#, only MSSQL.

as far as the entity framework is concerned, the two are basically interchangeable. that's the whole idea of adding the layer of ORM abstraction.

What does .Include() do?

if I were to say

Code:

user u = dbObject.users.Include("user_groups").FirstOrDefault();

it would include the list of user_group objects of which the user is a member. without the .Include(), it would leave the user_groups property set to null.

Better yet, what data does a 'user' object have after that chain of extension methods is called?

the user object should have a collection of site objects and a collection of user_group objects

the user_group objects will have collections of user_group_trans_type and user_group_user_activity objects

each user_group_trans_type object will have a reference to a single trans_type object

each user_group_user_activity object will have a reference to a single user_activity object.

I'm surprised that you are seeing the particular exception that you are getting, but I can tell you that Include is not intended to be used with that many related entities in a single query. Every Include statement you add to an EF query will add onto that query producing an ever growing set of joins. If you have too many this will make your query inefficient, and eventually it will even cause the EF and/or the database query processor to be unable to process the query.

Include makes queries easier to write when you have a few related entities to include, and it also has the nice advantage of keeping all of your data in a single query so it is guaranteed to all be consistent even in the face of others modifying the data at the same time that you are querying. If you truly have a lot of related entities to retrieve, though, you probably don't want or need that level of consistency (since it can be very inefficient on the server).

You are generally better off issuing a few queries rather than just one. The ObjectStateManager will match up related entities so after you are done executing the queries, you will have the relationships all fixed up properly even if you use multiple queries.

- Danny