Where clause gets "server side currently not implemented" error

6 posts, 0 answers
  1. Josh
    Josh avatar
    12 posts
    Member since:
    Aug 2008

    Posted 23 Jul 2009 Link to this post

    I am trying to run the following LINQ query agaist my object model.

    IQueryable<FailureReport> FRs = from c in scope.Extent<FailureReport>() select c;

    FRs = FRs.Where(c => ((c.ActionItems.Where(ai => ai.Assignee.ToLower().Contains(actionItemAssignee.ToLower()))).Count() >= 1));

    FRs = FRs.OrderBy(c => c.DateCreated);

     listOut = FRs.ToList();


    When I try to run the where clause to limit based on related table, assignee, I get the following error:

    - Exception occurred in GetFailureReports: System.NotSupportedException: Execution of 'System.Linq.Enumerable:Where(IEnumerable`1,Func`2)' on the database server side currently not implemented.  
    [1068]    at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQuery(Type type, Expression expression, Int32& number, Boolean exec, Int32 numSkip, Int32 numTake)  
    [1068]    at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQueryMultiple[T](Expression expression)  
    [1068]    at Telerik.OpenAccess.Query.ObjectScopeQuery`2.GetEnumerator()  
    [1068]    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)  
    [1068]    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)  
    [1068]    at ppiatm.QAReporter.Data.FailureReport.GetFailureReports(DateTime dtStart, DateTime dtEnd, String severity, String description, String project, String wellName, String frNumber, String rigName, String equipment, String contractor, String contractorRefNumber, String actionItemAssignee, IObjectScope scope) in C:\svn\QAReporter\QAReporterDataLayer\FailureReport.PPI.cs:line 204 
     

    Any ideas why this would not work or a workaround to allow me to filter based on a related table with one to many records.

    Thanks,

    Josh


  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 24 Jul 2009 Link to this post

    Hello Josh,

    Unfortunately Telerik OpenAccess ORM does not support executing queries with nested Where clauses on the server yet. I am afraid you will have to execute the query on the client as shown below:
    var FRs = scope.Extent<FailureReport>().ToList().Where(c => ((c.ActionItems.Where(ai => ai.Assignee.ToLower().Contains(actionItemAssignee.ToLower()))).Count() >= 1)).OrderBy(c => c.DateCreated); 
     
     listOut = FRs.ToList(); 
    Hope this helps.

    Kind regards,
    Alexander
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Redeeze
    Redeeze avatar
    4 posts
    Member since:
    Sep 2011

    Posted 11 Sep 2011 Link to this post

    "Unfortunately Telerik OpenAccess ORM does not support executing queries with nested Where clauses on the server yet."

    Is this still true?

    Thanks.
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Sep 2011 Link to this post

    Hi Matthew,

     good news! This has been fixed and I just verified this with

    string name = "Orlando";
     IQueryable<Region> FRs = from c in Scope.Extent<Region>() select c;
     FRs = FRs.Where(c => c.Territories.Where(ai => ai.TerritoryDescription.ToLower().Contains(name.ToLower())).Count() >= 1);
                                                                   
     FRs = FRs.OrderBy(c => c.Id);
     var listOut = FRs.ToList();

    which produced 

    SELECT a.[id] AS COL1, a.[region_description] AS COL2 FROM [region] a WHERE  (SELECT COUNT(1)                  FROM [territory] b WHERE a.[id] = b.[id3] AND lower(b.[territory_description]) LIKE '%' + ? + '%' ESCAPE '\')  >= 1 ORDER BY a.[id]
    The changed code is available in the latest service release.

    All the best,
    Thomas
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  6. AJ
    AJ avatar
    33 posts
    Member since:
    Jun 2011

    Posted 30 May 2012 Link to this post

    I do realize this is an old post; however, I'm having the same problem as described here and I'm using v2012.1.301.2.

    On a RadComboBox, I allow the user to enter multiple values separated by a comma. I get the items as they are requested:

    protected void rcbInvoiceNumber_OnItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)
    {
        var searchTerms = e.Text.Replace(", ", ",").Split(',').ToList();
     
        var invoices = _ctx.VInvoicesViewGroupeds
            .Where(i => i.IsPaid == 1 &&
                        i.IsArchived == false &&
                        i.InvoiceNumber.StartsWith(searchTerms.Last()));
                     
        rcbInvoiceNumber.DataSource = invoices.Skip(e.NumberOfItems).Take(25);
        rcbInvoiceNumber.DataBind();
     
        var endOffset = e.NumberOfItems + invoices.Count();
        var totalCount = invoices.Count();
     
        if (endOffset == totalCount)
            e.EndOfItems = true;
     
        e.Message = String.Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>", endOffset, totalCount);
    }

    However, I receive the error Failure: Execution of 'System.Linq.Enumerable:Last(IEnumerable`1)' on the database server side currently not implemented.

    If I create a variable "var searchTerm = searchTerms.Last()" and use searchTerm it works perfectly fine.
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 04 Jun 2012 Link to this post

    Thanks for reporting this issue. A fix for such client side evaluatable enumerable methods will be contained in the upcoming release 2012 Q2.
    The workaround is -as you described- to execute the method outside of the query (it's anyways faster in this case) and not try to push this to the server.

    I've updated your Telerik points.

    Best regards,
    Thomas
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
Back to Top
DevCraft banner