strangeness with entity framework

This is a discussion on strangeness with entity framework within the C# Programming forums, part of the General Programming Boards category; I have a database (mysql) for an account management system that contains a user table the user table references two ...

  1. #1
    Registered User
    Join Date
    Oct 2006
    Posts
    2,266

    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?
    Last edited by Elkvis; 06-03-2010 at 01:38 PM.

  2. #2
    ...and never returned. StainedBlue's Avatar
    Join Date
    Aug 2009
    Posts
    168
    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?
    goto( comeFrom() );

  3. #3
    Registered User
    Join Date
    Oct 2006
    Posts
    2,266
    Quote Originally Posted by StainedBlue View Post
    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.

  4. #4
    Registered User
    Join Date
    Jun 2010
    Posts
    3
    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

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. How does this Game Maker-like c program look
    By hahaguy in forum Game Programming
    Replies: 0
    Last Post: 04-23-2009, 06:32 AM
  2. entity framework and inserting rows
    By Elkvis in forum C# Programming
    Replies: 0
    Last Post: 04-01-2009, 07:36 AM
  3. Exception handling framework based on multiple inheritance
    By Mario F. in forum C++ Programming
    Replies: 11
    Last Post: 06-25-2007, 10:17 AM
  4. Unit Actions
    By DavidP in forum Game Programming
    Replies: 20
    Last Post: 05-28-2004, 09:18 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21