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

[IPV]

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!!

[Viktor Zhivkov]

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

 

[IPV]

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.

[Viktor Zhivkov]

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