Where clause gets "server side currently not implemented" error

[Josh]

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

[Alexander]

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.

[Redeeze]

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

Is this still true?

Thanks.

[Thomas]

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

[AJ]

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.

[Thomas]

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