OrderBy with contains and mapping fails duplicate Query source - expression association #12175

bmarder · 2018-05-30T19:44:12Z

Unhandled Exception: System.InvalidOperationException: Query source (from Guid <generated>_1 in __ids_0) has already been associated with an expression.
   at Remotion.Linq.Clauses.QuerySourceMapping.AddMapping(IQuerySource querySource, Expression expression)
   at Remotion.Linq.Clauses.MainFromClause.Clone(CloneContext cloneContext)
   at Remotion.Linq.QueryModel.Clone(QuerySourceMapping querySourceMapping)
   at Remotion.Linq.Clauses.ExpressionVisitors.CloningExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.ExpressionVisitors.CloningExpressionVisitor.AdjustExpressionAfterCloning(Expression expression, QuerySourceMapping querySourceMapping)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CorrelatedCollectionOptimizingVisitor.<>c__DisplayClass18_0.<Rewrite>b__1(Ordering o)
   at System.Linq.Enumerable.SelectListIterator`2.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CorrelatedCollectionOptimizingVisitor.Rewrite(Int32 correlatedCollectionIndex, QueryModel collectionQueryModel, INavigation navigation, Boolean trackingQuery, QuerySourceReferenceExpression originQuerySource, Boolean forceListResult)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CorrelatedCollectionOptimizingVisitor.TryRewrite(SubQueryExpression subQueryExpression, Boolean forceToListResult, Expression& result)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CorrelatedCollectionOptimizingVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.TryOptimizeCorrelatedCollections(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Remotion.Linq.Clauses.SelectClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass22_0`1.<CompileAsyncQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

using AutoMapper;
using AutoMapper.QueryableExtensions;

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;

namespace EFCoreBugs
{
    class Program
    {
        static async Task Main(string[] args)
        {
            using (var db = new TestDb())
            {
                await db.Database.EnsureDeletedAsync();
                await db.Database.EnsureCreatedAsync();
            }
            using (var db = new TestDb())
            {
                var ids = new List<Guid>();
                var units = await db.Blogs
                    .OrderByDescending(x => ids.Contains(x.Id))
                    .ProjectTo<BlogDto>()
                    .ToListAsync();
            }

            Console.ReadLine();
        }
    }

    public class TestDb : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var log = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });
            optionsBuilder.UseLoggerFactory(log);
            optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=master;Database=bugtest;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        }
    }

    public class Blog
    {
        public Guid Id { get; set; }

        [InverseProperty(nameof(Post.Blog))]
        public virtual List<Post> Posts { get; } = new List<Post>();

    }
    public class Post
    {
        public Guid Id { get; set; }
        public Guid BlogId { get; set; }

        [ForeignKey(nameof(BlogId))]
        public virtual Blog Blog { get; private set; }
    }

    public class BlogDto
    {
        public IEnumerable<PostDto> Posts { get; private set; }
    }
    public class PostDto { }
}

I had all of the above happen while trying to replicate a different error below. If somehow this works (with the mappings added), two SQL queries are generated which throws a completely different error. Also note, the following error only occurs if the ids list is empty.

SELECT [x].[Id]
      FROM [Blogs] AS [x]
      ORDER BY (SELECT 1) DESC, [x].[Id]

SELECT [t].[c], [t].[Id], [x.Posts].[BlogId]
      FROM [Posts] AS [x.Posts]
      INNER JOIN (
          SELECT 0 AS [c], [x0].[Id]
          FROM [Blogs] AS [x0]
      ) AS [t] ON [x.Posts].[BlogId] = [t].[Id]
      ORDER BY [t].[c] DESC, [t].[Id]

Unhandled Exception: System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Boolean' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Boolean'.
   at System.Data.SqlClient.SqlBuffer.get_Boolean()
   at lambda_method(Closure , DbDataReader )
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.CorrelateSubqueryAsync[TInner,TOut,TCollection](Int32 correlatedCollectionId, INavigation navigation, Func`2 resultCollectionFactory, MaterializedAnonymousObject outerKey, Boolean tracking, Func`1 correlatedCollectionFactory, Func`3 correlationPredicate, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.TaskLiftingExpressionVisitor._ExecuteAsync[T](IReadOnlyList`1 taskFactories, Func`2 selector)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.AsyncSelectEnumerable`2.AsyncSelectEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)

Further technical details

EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system:
IDE: (e.g. Visual Studio 2017 15.4)

The text was updated successfully, but these errors were encountered:

ajcvickers · 2018-05-31T17:32:23Z

@bmarder Any reason you are still using preview2? Can you update to the 2.1 RTM version and report back if you are still seeing the issue?

bmarder · 2018-06-01T13:09:23Z

@ajcvickers Well the version suggestion certainly explains why I was having trouble replicating in a new app. That bug has definitely been fixed in 2.1. Sorry about that.

I can now replicate the 2nd bug, ~~but only with using automapper, so I'm not sure if it's an EFCore bug or an Automapper bug.~~ Here is the repo.

@ajcvickers updated the repo without automapper

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;

namespace EFCoreBugs
{
    class Program
    {
        static async Task Main(string[] args)
        {
            using (var db = new TestDb())
            {
                await db.Database.EnsureDeletedAsync();
                await db.Database.EnsureCreatedAsync();

                var blog = new Blog();
                var post = new Post();
                blog.Posts.Add(post);
                db.Blogs.Add(blog);
                await db.SaveChangesAsync();
            }
            using (var db = new TestDb())
            {
                // throws only if list is empty
                var ids = new List<Guid>();

                await db.Blogs
                    .OrderByDescending(x => ids.Contains(x.Id))
                    .Select(blog => new BlogDto { Posts = blog.Posts.Select(_ => new PostDto()).ToList() })
                    .ToListAsync();
            }

            Console.ReadLine();
        }
    }

    public class TestDb : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var log = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });
            optionsBuilder.UseLoggerFactory(log);
            optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=master;Database=bugtest;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        }
    }

    public class Blog
    {
        public Guid Id { get; set; }

        [InverseProperty(nameof(Post.Blog))]
        public virtual List<Post> Posts { get; } = new List<Post>();

    }
    public class Post
    {
        public Guid Id { get; set; }
        public Guid BlogId { get; set; }

        [ForeignKey(nameof(BlogId))]
        public virtual Blog Blog { get; private set; }
    }

    public class BlogDto
    {
        public IEnumerable<PostDto> Posts { get; set; }
    }
    public class PostDto { }
}

System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Boolean' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Boolean'.
   at System.Data.SqlClient.SqlBuffer.get_Boolean()
   at System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i)
   at lambda_method(Closure , DbDataReader )
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.CorrelateSubqueryAsync[TInner,TOut,TCollection](Int32 correlatedCollectionId, INavigation navigation, Func`2 resultCollectionFactory, MaterializedAnonymousObject outerKey, Boolean tracking, Func`1 correlatedCollectionFactory, Func`3 correlationPredicate, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.TaskLiftingExpressionVisitor._ExecuteAsync[T](IReadOnlyList`1 taskFactories, Func`2 selector)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.AsyncSelectEnumerable`2.AsyncSelectEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)

ajcvickers · 2018-06-04T16:21:46Z

Notes for triage:

This also fails:

var query = db.Blogs
    .OrderByDescending(x => ids.Contains(x.Id))
    .Select(blog => new BlogDto { Posts = blog.Posts.Select(p => new PostDto() { Title = p.Title }).ToList() });

Removing ToList makes both the posted query and this one work.
As stated, if the list passed to Contains is not empty, then the query works.

ajcvickers · 2018-06-04T18:34:37Z

@maumar Can you investigate if this is a regression?

maumar · 2018-06-04T23:04:20Z

@ajcvickers this is not a regression, the exception was different in 2.0.2 but the scenario was failing nonetheless.

maumar · 2018-06-05T00:16:51Z

simpler query:

var ids = new List<Guid>();

var query = db.Blogs
    .OrderByDescending(x => ids.Contains(x.Id))
    .Select(b => b.Posts.Select(p => p.Id).ToList());

Correlated collection optimization is applied to this query, producing the following QM:

from Blog x in DbSet<Blog>
order by 
    (from Guid <generated>_1 in __ids_0
    select [<generated>_1]).Contains([x].Id) desc, (Nullable<Guid>)EF.Property(?[x]?, "Id") asc
select new BlogDto{ Posts = List<PostDto> queryContext.QueryBuffer.CorrelateSubquery(
        correlatedCollectionId: 0, 
        navigation: Blog.Posts, 
        resultCollectionFactory: (INavigation n) => new List<PostDto>(), 
        outerKey: new MaterializedAnonymousObject(new object[]{ (object)EF.Property(?[x]?, "Id") }), 
        tracking: False, 
        correlatedCollectionFactory: () => 
            from Post x.Posts in DbSet<Post>
            join MaterializedAnonymousObject _x in 
                from Blog x in DbSet<Blog>
                select new MaterializedAnonymousObject(new object[]
                { 
                    (object)
                        (from Guid <generated>_1 in __ids_0
                        select [<generated>_1]).Contains([x].Id), 
                    (object)(Nullable<Guid>)EF.Property(?[x]?, "Id") 
                })
            on Property([x.Posts], "BlogId") equals (Nullable<Guid>)object [_x].GetValue(1)
            order by object [_x].GetValue(0) desc, object [_x].GetValue(1) asc
            select new Tuple<PostDto, MaterializedAnonymousObject, MaterializedAnonymousObject>(
                PostDto, 
                new MaterializedAnonymousObject(new object[]{ (object)EF.Property(?[x.Posts]?, "BlogId") }), 
                new MaterializedAnonymousObject(new object[]{ object [_x].GetValue(1) })
            ), 
        correlationPredicate: (MaterializedAnonymousObject o | MaterializedAnonymousObject i) => object i.GetValue(0) == default(object) || object o.GetValue(0) == default(object) ? False : (Guid)object o.GetValue(0) == (Guid)object i.GetValue(0)) }

and 2 queries:

SELECT [x].[Id]
FROM [Blogs] AS [x]
ORDER BY (SELECT 1) DESC, [x].[Id]

SELECT [t].[c], [t].[Id], [x.Posts].[BlogId]
FROM [Posts] AS [x.Posts]
INNER JOIN (
	SELECT 0 AS [c], [x0].[Id]
	FROM [Blogs] AS [x0]
) AS [t] ON [x.Posts].[BlogId] = [t].[Id]
ORDER BY [t].[c] DESC, [t].[Id]

Problem here is that in the second query, from Guid <generated>_1 in __ids_0 select <generated>_1]).Contains([x].Id), is funcletized to constant false (if the list is empty). This gets rendered in the second query as 1, which is of type int, not bool. However the projection expects bool result, hence the type mismatch

If normal constant bool is projected in a subquery we have safeguards in SqlServerQIeruSqlGenerator that injects explicit cast. The problem here is that this code runs before the optimizations (which convert IN expression into constant bool).

…ry source - expression association Problem was that for queries that project constant bool which gets produced as a result of optimization (e.g. Contains on empty collection) we would not add explicit cast to bool, which resulted in value coming back as int. Fix is to apply optimizations before we perform the check that determines whether the explicit cast is needed - this way the value is already collapsed into constant at this point and explicit cast is correctly applied.

maumar · 2018-06-06T20:06:41Z

fixed in e18824e

…ry source - expression association Problem was that for queries that project constant bool which gets produced as a result of optimization (e.g. Contains on empty collection) we would not add explicit cast to bool, which resulted in value coming back as int. Fix is to apply optimizations before we perform the check that determines whether the explicit cast is needed - this way the value is already collapsed into constant at this point and explicit cast is correctly applied.

ajcvickers added the customer-reported label Jun 1, 2018

ajcvickers assigned maumar Jun 4, 2018

ajcvickers added type-investigation type-bug labels Jun 4, 2018

ajcvickers added this to the 2.1.3 milestone Jun 4, 2018

maumar removed the type-investigation label Jun 4, 2018

maumar modified the milestones: 2.1.3, 2.2.0 Jun 5, 2018

ajcvickers added the size~1-day label Jun 6, 2018

maumar closed this as completed Jun 6, 2018

maumar added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Jun 6, 2018

ajcvickers mentioned this issue Jun 7, 2018

SqlServer-2.1 - InvalidCastException (Int32 to Boolean) #12280

Closed

ajcvickers modified the milestones: 2.2.0-preview2, 2.2.0 Nov 11, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

OrderBy with contains and mapping fails duplicate Query source - expression association #12175

OrderBy with contains and mapping fails duplicate Query source - expression association #12175

bmarder commented May 30, 2018 •

edited

Loading

ajcvickers commented May 31, 2018

bmarder commented Jun 1, 2018 •

edited

Loading

ajcvickers commented Jun 4, 2018

ajcvickers commented Jun 4, 2018

maumar commented Jun 4, 2018

maumar commented Jun 5, 2018 •

edited

Loading

maumar commented Jun 6, 2018

OrderBy with contains and mapping fails duplicate Query source - expression association #12175

OrderBy with contains and mapping fails duplicate Query source - expression association #12175

Comments

bmarder commented May 30, 2018 • edited Loading

Further technical details

ajcvickers commented May 31, 2018

bmarder commented Jun 1, 2018 • edited Loading

ajcvickers commented Jun 4, 2018

ajcvickers commented Jun 4, 2018

maumar commented Jun 4, 2018

maumar commented Jun 5, 2018 • edited Loading

maumar commented Jun 6, 2018

bmarder commented May 30, 2018 •

edited

Loading

bmarder commented Jun 1, 2018 •

edited

Loading

maumar commented Jun 5, 2018 •

edited

Loading