Filtering child objects when eager loading via a join

3 posts, 0 answers
  1. Andrew
    Andrew avatar
    8 posts
    Member since:
    Jan 2013

    Posted 18 May 2013 Link to this post

    As my application grows, lazy loading will start to become a performance hit so I'm looking into selectively eager loading some of the data. I'm familiar with the fetch plan API, and have implemented that in certain sections of the code. However, I would like to be able to filter the child objects in the query.

    For example, I have Post table and a Comment table which are related (a post can have many comments). Currently I can get a list of posts and all of their comments via fetch plans, but I would like to be able to apply a filter (lambda expression) to specify which comments I want loaded with the posts. (i.e. I just want to display the top 10 comments first, and then load the rest via ajax if the user wants).

    I was given a couple solutions in reply to my comment here:

    "The first option is to create 2 LINQ statements - the first one retrieving the needed post(s) and the second one getting the comments with additional filtering. The second option is to filter both in a single LINQ statement with a join."

    The first option is more scalable than lazy loading, but would effectively boil down to a "where comment_id in (*list of post ids*)" query which would fail for a very large amount of posts. The second option is great, but can I map that LINQ join query to OpenAccess domain classes?

    Thanks for any help, I'm really liking OpenAccess so far. This is the only thing I'm having trouble working around cleanly.
  2. Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 22 May 2013 Link to this post

    Hi Andrew,

    A query like this one you want to execute should be translated  to LEFT JOIN  sql statement which could lead to performance problems because a lot of the returned data will be redundant. Especially if you are retrieving large amount of data. 

    In general you can consider redesign in your application in order to avoid such cases. It will be faster if you can retrieve you list of Posts without the Comments collection populated and retrieve it later on demand for a particular Post (if possible in you scenario).

    We will analyze you case in more details and I'll get back to you with better suggestion as soon as possible. 

    Regards,
    Kaloyan Nikolov
    Telerik
    OpenAccess Samples Kit boasts 50+ sample applications providing diverse real-life business solutions. Click to read more and see OpenAccess ORM in action.
  3. DevCraft banner
  4. Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 23 May 2013 Link to this post

    Hi Andrew,

    Here as an example of equivalent to yours linq query which results in LEFT JOIN SQL statement filtering only the last 10 records of the slave collection. 

    using (var dbContext = new EntitiesModel1())
    {
        var books = (from b in dbContext.Books
                     join l in dbContext.Loans on new { pk = b.Id, max = true }
                     equals new { pk = l.Book_id, max = l.Id + 10 > dbContext.Loans.Where(x => x.Book_id == l.Book_id).Max(x => x.Id) } into jointable
                     from x in jointable.DefaultIfEmpty()
                     select new { Title = b.Title, Date = x.Loan_start }).ToArray();
     
        //this operation is perfroming client side grouping
        var groupedBooks = (from b in books
                            group b by b.Title into g
                            select new { book = g.Key, loans = g.Where(l => l.Date != DateTime.MinValue).ToArray() }).ToArray();
    }


    The query has the following limitations:

    1. It takes the last 10 records of the slave collection based on the ID, the assumption is that the Ids are sequential and there are no deleted records and missing ids in the database.

    2. You cannot select entire object in the anonymous type (new { Title = b.Title, Date = x.Loan_start }), you should add all of the fields you need, the minimum possible is better concerning the perfromance

    3. The last grouping should be done in your application instead of in the backend. 

    The resulting sql query is really heavy and I would suggest you the rethink the way you retrieve/present the information if possible. With large amount of data in the tables this could cause performance problems.

    I hope this helps. Do not hesitate to contact us again with any further questions.

    Regards,
    Kaloyan Nikolov
    Telerik
    OpenAccess Samples Kit boasts 50+ sample applications providing diverse real-life business solutions. Click to read more and see OpenAccess ORM in action.
Back to Top