Question About DataModel and LINQ Sort/Where claus

3 posts, 0 answers
  1. Jesse Flint
    Jesse Flint avatar
    4 posts
    Member since:
    Jun 2008

    Posted 28 Sep 2010 Link to this post

    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


     

  2. Jesse Flint
    Jesse Flint avatar
    4 posts
    Member since:
    Jun 2008

    Posted 28 Sep 2010 Link to this post

    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)
  3. DevCraft banner
  4. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 04 Oct 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top