locked
Entity Framework - Loading related entities RRS feed

  • Question

  • I am using the entity framework to manipulate large ammounts of data. I am new to the entity framework so please bare  with me.

    The way I have designed the database model context, I am relating a User entity loaded from the database (as the logged user) and I am trying to bind the related Tickets that this instance contains.

    var tickets = (from p in User.Tickets
                        where p.Date != DateTime.Now.Date
                        orderby p.Date descending
                        select p).Skip<Ticket>(0).Take<Ticket>(10);

    Then I am binding it with a wpf list view

    ticketList.ItemsSource = tickets;

    Finally I am Lazy loading the related data

    User.Tickets.Load();

    Binding to a small amount of data returned takes no time at all.

    Changing the schema, this query without any limits would return more than 20000 results (tickets). When doing so, even if I use the Skip and Take methods the application hangs at the Load() statement and the results show up hours latter...

    I appreciate any help and every suggestion that can point me to the right direction.

    Thank you
    Wednesday, February 10, 2010 12:50 PM

Answers

  • Not sure if I was not able to convey my point successfully in my earlier post or we are thinking in opposite directions as my answer is still similar.

    * If you want to load ALL the tickets associated with a user, try user.Tickets.Load(). I dont think there is any way to load subset of child entries via Load() method.

    * However, to load a subset of tickets, you can try:

    //user for which you want to retrieve tickets
    var myUser = ...; 
    
    //build a query to get 10 latest tickets for that particular user
    var query = (from t in db.Tickets where t.User.UserID == myUser.UserID select t).OrderByDescending(t=>t.Date).Take(10);
    
    //run the query to get the tickets (EF will relate to that user automatically)
    query.ToList();
    
    //now user.Tickets will have those 10 records
    Debug.Assert( myUser.Tickets.Count == 10 );
    


    The interesting point is that Entity Framework will automatically relate the above ticket records to the respective user and user.Tickets() will now have a collection of 10 records. This is how you can query for a subset of tickets for a particular user.

    Let me know if this answers your question.

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Friday, February 12, 2010 7:13 PM

All replies

  • I am using the entity framework to manipulate large ammounts of data. I am new to the entity framework so please bare  with me.

    The way I have designed the database model context, I am relating a User entity loaded from the database (as the logged user) and I am trying to bind the related Tickets that this instance contains.

    var tickets = (from p in User.Tickets
                        where p.Date != DateTime.Now.Date
                        orderby p.Date descending
                        select p).Skip<Ticket>(0).Take<Ticket>(10);

    Then I am binding it with a wpf list view

    ticketList.ItemsSource = tickets;

    Finally I am Lazy loading the related data

    User.Tickets.Load();

    Binding to a small amount of data returned takes no time at all.

    Changing the schema, this query without any limits would return more than 20000 results (tickets). When doing so, even if I use the Skip and Take methods the application hangs at the Load() statement and the results show up hours latter...

    I appreciate any help and every suggestion that can point me to the right direction.

    Thank you
    Anyone? Any ideas? Please I would really appreciate any help, because I really enjoy the database layer OO feeling when using the EF. Using the datasets produced and quering that instead of the User.Ticket reminds me of typed datasets and I dont want to go back to using mine "entity framework" that was a combination of inherited objects and static sql queries.

    Thank you
    Thursday, February 11, 2010 11:32 AM
  • Hi,

    If I got you correct, your problem is that user.Tickets.Load() loads ALL (e.g. 20,000) tickets records but you want to load some. If this is correct, then you can take advantage of EF's automatic relationship building. So instead of calling user.TIckets.Load(), you need to run a query that returns a subset of Ticket objects like this:

    var query =
     (
     from t in dc.Tickets
     where t.UserID == myUserID
     select t).Take(10);

    Also, have a look at some techniques for conditional include in this thread and this blog post.

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Friday, February 12, 2010 6:47 AM
  • Thank you for your reply,

    I just can't get it thought. If I can only do this using the "dataset" entity collection of the tickets, whats the point of the related entity reference/reference to a collection?

    I can get the related collection of tickets, the only thing I want more is to limit that collection, so that not every ticket in the collection gets initialized and the application hangs trying to instantiate all the related tickets.

    The way (I think) it works as it is in my first post is that it loads all the related data and then it applies the limit, and I am saying that because I used the database profile and the query stays the same with or without the Skip and Take.

    There are case where I only want to know about the User and still query his collection of tickets, and not having to use the collection of the all the tickets in the system.

    In my head, this is what I want from the entity framework, cause quering the datasets and figuring out the relationships, is no differnent from using linq to sql or typed dataset.

    I appreciate any thoughts because I think I am missing something.

    Thank you.
    Friday, February 12, 2010 11:37 AM
  • Not sure if I was not able to convey my point successfully in my earlier post or we are thinking in opposite directions as my answer is still similar.

    * If you want to load ALL the tickets associated with a user, try user.Tickets.Load(). I dont think there is any way to load subset of child entries via Load() method.

    * However, to load a subset of tickets, you can try:

    //user for which you want to retrieve tickets
    var myUser = ...; 
    
    //build a query to get 10 latest tickets for that particular user
    var query = (from t in db.Tickets where t.User.UserID == myUser.UserID select t).OrderByDescending(t=>t.Date).Take(10);
    
    //run the query to get the tickets (EF will relate to that user automatically)
    query.ToList();
    
    //now user.Tickets will have those 10 records
    Debug.Assert( myUser.Tickets.Count == 10 );
    


    The interesting point is that Entity Framework will automatically relate the above ticket records to the respective user and user.Tickets() will now have a collection of 10 records. This is how you can query for a subset of tickets for a particular user.

    Let me know if this answers your question.

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Friday, February 12, 2010 7:13 PM
  • I can live with that for now, but hopefully this may be a feature of EF 4, cause really it is something I expect from EF...related entities and collections that can be linq-ed. Syed Mehroz Alam your solution sits in the middle of what I was looking for, which means for whatever I want to do with a list o tickets I have to query the ticketset so that my context has the tickets I need before I can access them throught another relationship.

    It would be great if Load could get arguments so that one can say which of the related entities he wants to be returned. 
    Syed Mehroz Alam I am marking you solution as an answer, however I if there is another way of loading a related entity collection, closer to what I was trying to do in my first post, I welcome it.

    Thank you.
    Sunday, February 14, 2010 11:11 AM
  • Hi

    Is that what you are looking to do? It will only load tickets for the users in which you queried for.
    Because the users are related to the tickets once they are loaded the user->ticket relationship will be made.

    //Load users
    var users = from _user in entities.Users where _user.Name == "Greg" select _user;
    //Get the user ids that are related to the tickets
    var userIds = new List<int>();
    //Add the user ids to a list.
    foreach (var user in users)
    {
        userIds.Add(user.Id);
    }
    //Load tickets related to the loaded users
    var tickets = from _ticket in entities.Tickets where userIds.Contains(_ticket.Id) select _ticket;
    Friday, August 13, 2010 8:02 PM
  • I think you can use "from p in User.Tickets.CreateSourceQuery().where....." to solve the all related entitites loading problem. CreateSourceQuery returns a ObjectQuery. 
    Sunday, April 24, 2011 1:58 PM
  • I guess it is too late to comment on this question, but I can see that you are not providing qualification to limit the retrieved users to a desired specific user. This might cause all users and their tickets to be returned which may be a large set to transmit back to the client, hence the noticed delay. Also, the use of "!=" prevents indexes (if nay) to be utilized. You also would have to build an index on the FK.
    Sunday, May 27, 2012 10:21 AM
  • Microsoft database engines before SQL Server 2012 did not offer a simple way to limit a query result with an OFFSET .... TAKE Syntax. For paging a large set  where a cursor was not an option, it was common to wrap the query with one that specifies an ordering and a generated ROW_NUMBER column which in turn would be used to limit the result set.

    Looks ugly but runs surprisingly performant.

    Entity Framework (up to 4 i am sure) provides no mapping of Skip() and Take() to SQL syntax, neither in LINQ nor in Fluent API writing style. In your example the *WHOLE* 20,000 item result set would be loaded from database into the client's memory, and *THEN* Skip() and Take() applied to the in-memory collection, hence the horrible performance.

    When I encountered this issue in a project I switched to load data into a web GUI using AJAX and ADO.NET application server code. I found no other workaround at the time - however, since SQL Server 2012 adds the syntax to limit result sets easily, a more recent EF version may add the mapping. An orderby clause probably would be mandatory.

    Note also, LINQ to SQL does *not* suffer the problem, here SQL Syntax is sent to the Server that handles the issue.

    MiB.

     

    Thursday, August 23, 2012 2:41 PM