Question About DataModel and LINQ Sort/Where claus

[Jesse Flint]

So Ive recently switched to the latest revision of Telerik ORM and this time when I enable a .NET 4.0  Data Access Layer project with Telerik.. I get a DataModel (Entity Model).   Which has been very nice.. and has worked great for me using an Oracle backend.
But I have this one issue im running into.

Normally in my DAL methods, Ill start with a base IQuerable or generic "var" type object and then based on search/filter criteria I will append new query values on..

In one example I am startinging with this type of query:

 

 

using (ORMDBCONNECTION oDB = new ORMDBCONNECTION())
{
    IQueryable<APPLICATION> qUery = (from app in oDB.Applications
       join firm in oDB.Applicants on app.APPLICANT_ID equals firm.APPLICANT_ID
       where app.EXPIRATION_DATE >= DateTime.Now
       select app);
}

and then if the SEARCH options are supplied I may want to narrow down the query.. like so:

qUery = qUery.Where(c=>c.APP_TYPE == "SOMEVAL");

or if a sort expression is added from the Telerik grid.. Id like to do something like:

qUery = qUery.OrderBy(c => c.APPLICANT.NAME);

 

 

The Problem is, when I run the method (the .Where() or the .OrderBy())... it seems to be going through every object.. every thing runs fine and quick and as expected as long as I keep the criteria within the confines of that FIRST IQueryable expression...   If I try to append to it or perform code like I listed above, it takes VERY VERY long and to me seems like its probably looping through every record.
Oddly enough, even though WHERE and OrderBy perform badly... the SKIP() method works fine and as expected.
like this: (works fine)
if (MAXROWS > 0) //Check to see if PAGING is required.. if so, only return needed rows.  

{

    qUery = qUery.Skip(STARTINDEX).Take(MAXROWS);

}

 

 

 

Is there something im missing about using .Where() and .OrderBy() with the Telerik ORM data Model?

Thanks in Advance to anyone here..

-Jesse

 

[Jesse Flint]

So let me rephrase the code example:

if I type this

var query = (from o in oDB.Table
                    where o.Name == "SOMEVAL"
                    select o);
That works great and returns the search quickly;

but if I do this instead:
var query = (from o in oDB.Table
                    select o);
query = query.Where(o=>o.Name == "SOMEVAL");

It takes a reallllllly long time and makes me think its getting the first values back then looping through them via LINQ to Objects instead of dynamically creating the SQL statement (IQuerable object)

[Petko_I]

Hello Jesse Flint,

I have investigated the problem you are experiencing. As far as I saw the delay comes from the join in the query. I did not discover any problems with a query of this kind

var query = (from o in oDB.Table
             select o);
query = query.Where(o=>o.Name == "SOMEVAL");

However, if I add a join in the first statement, the execution is delayed. The generated SQL is different in both cases (applying a filter in and outside the original query definition) and we will look into what options we have for improving the performance.
In case you have an association between Applications and Applicants, you can benefit from traversing the relationship instead of using a join. I am not sure if using dynamic LINQ expressions will make any difference on the performance.

The invocation of methods on a query after a select is performed results in additional statements in the database server. This is because a select can return an anonymous type or a custom type which is not persistent and subsequent filtering forces the final parts of the query to be executed in memory. What can be done to improve the performance on the final query is delay the select clause until you have applied all the filtering and ordering. This can be achieved (depending on the complexity) with using a method syntax from the very start of the building the query.

context.Applications.Join().Where().OrderBy().Where()…Select()

Feel free to contact us if you have further questions.

Sincerely yours,
Petko_I
the Telerik team