Query: enable synthesizing rows from list/array of constant values to allow more queries to be server-evaluated

[felipepessoto]

Steps to reproduce

string[] termsSplit = terms.Split(' ');
IQueryable<Post> query2 = 
    from post in db.Posts
    where termsSplit.Any(x => post.Content.Contains(x))
    select post;

The issue

query2.Count() causes a NullReferenceException. But it shouldn't be evalute that query at client side:

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=Anonymously Hosted DynamicMethods Assembly
  StackTrace:
       at lambda_method(Closure , String )
       at System.Linq.Enumerable.WhereArrayIterator`1.MoveNext()
       at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
       at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
       at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
       at lambda_method(Closure , QueryContext )
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
       at FujiyBlog.Web.Controllers.SearchController.Index(Nullable`1 page, String terms) in D:\Meus Arquivos\Desenvolvimento\Projetos\FujiyBlog\src\FujiyBlog.Web\Controllers\SearchController.cs:line 42
       at lambda_method(Closure , Object , Object[] )
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()
  InnerException: 

[rowanmiller]

@fujiy what version are you using? (1.0 or nightly 1.1 build)

[felipepessoto]

1.0. Should I try with daily build?

[maumar]

Any + Contains is not the problem here. Problem is the fact that termsSplit is a client side variable. In order to make this work on the server we would have to construct a row from a list of constants, which we currently don't do. Something like what EF6 does:

SELECT N'Foo' AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
UNION ALL
SELECT N'Bar' AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]

If we apply Any + Contains on a navigation property, e.g.:

from b in ctx.Blogs
where b.Posts.Any(p => p.Content.Contains(p.Foo))
select b;

we get a reasonable sql:

SELECT [b].[Id]
        FROM [Blogs] AS [b]
        WHERE EXISTS (
            SELECT 1
            FROM [Posts] AS [p]
            WHERE ((CHARINDEX([p].[Foo], [p].[Content]) > 0) OR ([p].[Foo] = N'')) AND ([b].[Id] = [p].[BlogId]))

[maumar]

sending for re-triage

[gojanpaolo]

I believe this is the issue we're having right now.

var words = new[] { "foo", "bar" };
ctx.Post.Where(p => words.Any(p.Title.Contains));

We used SearchExtensions as a workaround. But hopefully translating Any + Contains gets built-in ef core as well.

ctx.Post.Search(p => p.Title).Containing(words);

[nbelley]

I used raw sql queries instead... kind of sucks to be forced to do that!

[roji]

Duplicate of #30426