This is a migrated thread and some comments may be shown as answers.

Filtering child objects when eager loading via a join

2 Answers 135 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Andrew
Top achievements
Rank 1
Andrew asked on 18 May 2013, 02:12 PM
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 Answers, 1 is accepted

Sort by
0
Kaloyan Nikolov
Telerik team
answered on 22 May 2013, 07:28 PM
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.
0
Kaloyan Nikolov
Telerik team
answered on 23 May 2013, 11:44 AM
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.
Tags
Development (API, general questions)
Asked by
Andrew
Top achievements
Rank 1
Answers by
Kaloyan Nikolov
Telerik team
Share this question
or