12

I am getting runtime error

This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code.

I am trying to produce query that rather than filtering on every search criteria would include by adding all matching records on all search fields (would do OR rather than AND).

public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    var results = Enumerable.Empty<T>().AsQueryable();
    if (search != null)
    {
        if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
        {
            results = results.Union(queryable.SearchByPolicyNumber(search));
        }

        if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
        {
            results = results.Union(queryable.SearchByUniqueId(search));
        }

        if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
        {
            results = results.Union(queryable.SearchByPostCode(search));
        }
    }

    return results;
}

Mechanism started failing when I introduced var results = Enumerable.Empty<T>().AsQueryable(); which I need to start from something empty.

How do I start out from an empty set and then build Linq-to-sql results on top?

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • 1
    A guess: `queryable.Take(0)` might do the trick. It might be a good idea to point out ***exactly*** where this exception is occurring. – spender Jun 14 '16 at 14:49
  • @spender right before `.ToList()` – Matas Vaitkevicius Jun 14 '16 at 14:53
  • @spender This query is going to be materialized somewhere else, even though the problem is here. Where it gets materialized isn't really relevant; he did his job well by determining the source of the problem and only showing that. – Servy Jun 14 '16 at 14:55
  • @spender the one that gets called right before passing data to View, it's all in expression trees till that point. – Matas Vaitkevicius Jun 14 '16 at 14:57

3 Answers3

18

you can refactor the code to not need an empty set by only ever unioning results that you have:

public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    var subQueries = new List<IQueryable<T>>();
    if (search != null)
    {
        if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPolicyNumber(search));
        }

        if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByUniqueId(search));
        }

        if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPostCode(search));
        }
    }

    return subQueries.DefaultIfEmpty(queryable)
        .Aggregate((a, b) => a.Union(b));
}
Servy
  • 202,030
  • 26
  • 332
  • 449
  • Hi Servy. When I saw the List being introduced - I was like "what is he thinking! A List!"? But then I realized what you did, I must say - this is magnificent... :) Thank you. – Matas Vaitkevicius Jun 14 '16 at 15:03
  • @IvanStoev you could add a `DefaultIfEmpty` to define the behavior. It'd depend on what the specs want it to be, if it's even a supported case. My initial suspicion is that it'd want to use `queryable` as the default value. – Servy Jun 14 '16 at 15:13
  • @IvanStoev in my case I returned `return subQueries.Any() ? subQueries.Aggregate((a, b) => a.Union(b)) : queryable;` – Matas Vaitkevicius Jun 14 '16 at 15:22
  • I had an XML column in my tables and thus wound up using Concat due to Union trying to 'Distinct' behind the scenes (from what I could tell): 'code' if (subQueries.Count > 0) { var finalQuery = subQueries.DefaultIfEmpty().Aggregate((resultQuery, addedQuery) => resultQuery.Concat(addedQuery)); return finalQuery; } else { return Enumerable.Empty().AsQueryable(); } – Austin Rhymer Jul 22 '20 at 21:13
  • @AustinRhymer In this case the requirements of the question were specifically to union a number of queries not known at compile time. If you happen to need to do a different operation on an unknown number of queries, then you would indeed need to alter the solution accordingly. – Servy Jul 22 '20 at 21:16
2

Temporary hack that I have used

is to change from 

var results = Enumerable.Empty<T>().AsQueryable();

to

var results = queryable.Where(o => false);
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
0
var results = queryable.Take(0);

If there is at least one Union to the result, then this first empty set of results is ignored and the generated SQL query doesn't contain it. So if you call Union with this empty set of results, in the generated SQL, there is no union at all. But if you'd not call the Union, then it still calls the database, even there is WHERE (1=0). In such case it is better then to return an empty enumerable or a list to avoid not necessary DB call.

Froggy
  • 1
  • 2