This is a migrated thread and some comments may be shown as answers.
Failure: Execution of 'System.Linq.Enumerable:Any(IEnumerable`1,Func`2)' on the database server side currently not implemented.
3 Answers 35 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Kenb
Top achievements
Rank 1
Kenb asked on 28 Jul 2015, 03:59 PM

I am trying to execute this query:

 

from i in dbContext.Interviews
    .Include(r => r.InterviewRooms)
    .Where(i =>
        (i.InterviewRooms.Any(ir => roomIds.Any(ir.RoomId.ToString().Contains))
            || roomIds.Count == 0)
        &&
        (dates.Contains(i.WhenCreated.Date)
            || dates.Count() == 0))
    orderby i.WhenCreated descending
    select i;

but am getting this error:

Failure: Execution of 'System.Linq.Enumerable:Any(IEnumerable`1,Func`2)' on the database server side currently not implemented.

The purpose of the line with the .Any() is to find if a list contains any elements present in a second list (check the RoomId of each InterviewRoom to see if it is contained in a list of wanted roomIds).

How should I be writing that query so that DataAccess likes it?

 

Thanks!!

3 Answers, 1 is accepted

Sort by
0
Viktor Zhivkov
Telerik team
answered on 31 Jul 2015, 07:42 AM
Hello Morgan,

The query that you have posted is not a valid LINQ as you are passing in your second .Any() a delegate to .Contains() method without specifying any arguments.
Given the limited information I am not able to help you find a different way to express the query so can you please provide us with the following information:
1 - What is the intent of the query?
2 - What is the data model - is the relationship between Interview and InterviewRoom one to many or many to many (with join table). Is there anything else in-between them?

We are looking forward the additional information that you can send us.

Regards,
Viktor Zhivkov
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Kenb
Top achievements
Rank 1
answered on 31 Jul 2015, 02:48 PM

There is a table called InterviewRooms and a second table called Interviews that contains a collection of InterviewRooms.

Within my application the user selects from a multi-select drop down list to create a List<Guid> called roomsIds. These are the Ids of the rooms the user is interested in.

The Where clause is supposed to find all members of Interviews where Interviews.InterviewRooms contains a room with an Id that also exists in roomIds.

0
Viktor Zhivkov
Telerik team
answered on 05 Aug 2015, 01:25 PM
Hi Morgan,

One possibility to redefine the query is this:
01.var query = from i in dbContext.Interviews.Include(r => r.InterviewRooms)
02.        // add the other part of the Many-to-Many relation
03.        from ir in dbContext.InterviewRooms
04.        // "join" both sides
05.        where i.InterviewRooms.Contains(ir) &&
06.            // filter by the roomIds list
07.            (roomIds.Contains(ir) || roomIds.Count == 0) &&
08.            // filter by the dates list
09.            (dates.Contains(.WhenCreated.Date) || dates.Count == 0)
10.        orderby i.WhenCreated descending
11.        select i;

Please note that I have not tested the exact query as I am lacking your data model.
Additionally you should know that we have introduced special handling of IEnumerable.Contains (roomIds.Contains() and dates.Contains() calls in your case) that includes IN clause or in case with high number of values in the lists a temporary table.
You may want to explore the SQL that is generated in your scenario and compare its performance against hand-crafted SQL query. If you see significant performance differences I would suggest that you rewrite your query as a Stored Procedure or use our ADO API to execute the raw SQL to perform the same calculations.

If you are experiencing any issue with the suggested query, do not hesitate to contact us.

Regards,
Viktor Zhivkov
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
LINQ (LINQ specific questions)
Asked by
Kenb
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Kenb
Top achievements
Rank 1
Share this question
or