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

4 posts, 0 answers
  1. Morgan
    Morgan avatar
    15 posts
    Member since:
    Nov 2014

    Posted 28 Jul 2015 Link to this post

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

  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 31 Jul 2015 Link to this post

    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.
  3. DevCraft banner
  4. Morgan
    Morgan avatar
    15 posts
    Member since:
    Nov 2014

    Posted 31 Jul 2015 in reply to Viktor Zhivkov Link to this post

    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.

  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 05 Aug 2015 Link to this post

    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.
Back to Top