Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query: ArgumentException when chaining projections (best guess) #6938

Closed
kierenj opened this issue Nov 4, 2016 · 5 comments
Closed

Query: ArgumentException when chaining projections (best guess) #6938

kierenj opened this issue Nov 4, 2016 · 5 comments
Assignees
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. punted-for-2.0 punted-for-2.1 type-bug
Milestone

Comments

@kierenj
Copy link

kierenj commented Nov 4, 2016

Steps to reproduce

Trying to work around the other issue I just raised by grabbing the whole DB model into memory and manually querying. New internal exception with that one too unfortunately.

Same model as #6937 .

            var model = await _context.Set<Image>()
                .Where(i => i.Retired == null)
                .Select(i => new
                {
                    id = i.Id,
                    tags = i.Tags.Select(tl => tl.Tag).Where(t => t.Retired == null).Select(t => t.Id),
                    usages = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null)
                })
                .Select(i => new
                {
                    id = i.id,
                    imagetags = i.tags,
                    bonds = i.usages.Select(u => u.Bond.Id).Distinct(),
                    joints = i.usages.Select(u => u.Joint.Id).Distinct(),
                    usagetags = i.usages.SelectMany(u => u.Tags).Select(t => t.Tag).Where(t => t.Retired == null).Select(t => t.Id).Distinct(),
                    colours = i.usages.SelectMany(u => u.Widget.Colours).Select(c => c.Colour).Where(c => c.Retired == null).Select(c => c.Id).Distinct(),
                    textures = i.usages.Select(u => u.Widget.Texture.Id).Distinct(),
                    shapes = i.usages.Select(u => u.Widget.Shape).Distinct(),
                    sources = i.usages.Select(u => u.Widget.Source).Distinct(),
                    types = i.usages.Select(u => u.Widget.Type).Distinct()
                }).ToArrayAsync();

I'm chaining selects in order to define a subexpression (usages in the first projection) to avoid repeating it in the second.

The issue

Exception:

Exception thrown: 'System.ArgumentException' in System.Private.CoreLib.ni.dll

Additional information: An item with the same key has already been added. Key: join WidgetUsage ub.WidgetUsage in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MyProj.EntityModel.WidgetUsage]) on Property([ub], "WidgetUsageId") equals Property([ub.WidgetUsage], "Id")

Stack trace:

   at System.ThrowHelper.ThrowAddingDuplicateWithKeyArgumentException(Object key)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.SnapshotQuerySourceMapping(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.OptimizeJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index, Action baseVisitAction, MethodInfo operatorToFlatten, Boolean groupJoin)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, 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.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, 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.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ProjectionExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
   at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.VisitNew(NewExpression expression)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, 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.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__129`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToArrayAsync>d__130`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MyProj.Controllers.QueryController.<RunQuery>d__3.MoveNext() in C:\git\kierenj\MyProj-platform\src\MyProj\Controllers\QueryController.cs:line 49

Further technical details

EF Core version: 1.1.0-preview1-*
Operating system: Windows 10
Visual Studio version: n/a (dotnet, 1.1 Preview 1)

@kierenj kierenj changed the title ArgumentException when ArgumentException when chaining projections (best guess) Nov 4, 2016
@divega divega added this to the 1.2.0 milestone Nov 4, 2016
@kierenj
Copy link
Author

kierenj commented Nov 5, 2016

When I remove the chained projections, I get a new error:


            var model = await _context.Set<Image>()
                .Where(i => i.Retired == null)
                .Select(i => new
                {
                    id = i.Id,
                    imagetags = i.Tags.Select(tl => tl.Tag).Where(t => t.Retired == null).Select(t => t.Id).ToArray(),
                    bonds = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Bond.Id).Distinct().ToArray(),
                    joints = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Joint.Id).Distinct().ToArray(),
                    usagetags = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).SelectMany(u => u.Tags).Select(t => t.Tag).Where(t => t.Retired == null).Select(t => t.Id).Distinct().ToArray(),
                    colours = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).SelectMany(u => u.Widget.Colours).Select(c => c.Colour).Where(c => c.Retired == null).Select(c => c.Id).Distinct().ToArray(),
                    textures = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Widget.Texture.Id).Distinct().ToArray(),
                    shapes = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Widget.Shape).Distinct().ToArray(),
                    sources = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Widget.Source).Distinct().ToArray(),
                    types = i.UsedWidgets.Select(ub => ub.WidgetUsage).Where(bu => bu.Retired == null && bu.Widget.Retired == null).Select(u => u.Widget.Type).Distinct().ToArray()
                }).ToArrayAsync();

Exception

Exception thrown: 'System.AggregateException' in System.Private.CoreLib.ni.dll

Additional information: One or more errors occurred.

Stack trace:

   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.<ToEnumerable_>d__133`1.MoveNext()
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source, Int32& length)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at lambda_method(Closure , ValueBuffer )
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__129`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToArrayAsync>d__130`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MyProj.Controllers.QueryController.<RunQuery>d__3.MoveNext() in C:\git\kierenj\MyProj-platform\src\MyProj\Controllers\QueryController.cs:line 73

One inner exception:

Object reference not set to an instance of an object.

Stack trace:

   at lambda_method(Closure , TransparentIdentifier`2 )
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()

@kierenj
Copy link
Author

kierenj commented Nov 5, 2016

If I cut down my original query to the point where it runs, any collections of Guids I get back are locked up in some proxy class from EF which I can't inspect with the debugger ("requires all threads to run") - hence trying ToArray(). Unfortunately, ToArray() generates exceptions too.

Intellisense (and the compiler) agree there's a coercion operator, but when I again try to cast my property projections via (Guid[]) I get a runtime exception from EF.

Happy to post more details as necessary - if there' a workaround in the meantime I'd be very grateful. Maybe it's just my model, but I'm really struggling to wrangle EF to do anything this week!

@maumar
Copy link
Contributor

maumar commented Apr 12, 2017

When trying to reproduce, consider using this more "complete" model: #7102

@maumar maumar assigned maumar and unassigned anpete Apr 13, 2017
@maumar
Copy link
Contributor

maumar commented Apr 14, 2017

We now throw different exception (dangling qsre)

Simplified repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var query = ctx.WidgetImages
                    .Select(i => new
                    {
                        usages = i.UsedWidgets.Select(ub => ub.WidgetUsage)
                            .Where(bu => bu.Retired)
                    })
                    .Select(i => new
                    {
                        bonds = i.usages.Select(u => u.Bond.Id).Distinct(),
                        joints = i.usages.Select(u => u.Joint.Id).Distinct(),
                    });

                var result = query.ToList();
            }
        }
    }


    public class MyContext : DbContext
    {
        public DbSet<WidgetImage> WidgetImages { get; set; }
        public DbSet<ImageWidgetUsageLink> ImageWidgetUsageLinks { get; set; }
        public DbSet<WidgetUsage> WidgetUsages { get; set; }
        public DbSet<Bond> Bonds { get; set; }
        public DbSet<Joint> Joints { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro6938;Trusted_Connection=True;MultipleActiveResultSets=True");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ImageWidgetUsageLink>().HasKey(l => new { l.ImageId, l.WidgetUsageId });
            modelBuilder.Entity<ImageWidgetUsageLink>().HasOne(l => l.Image).WithMany(i => i.UsedWidgets).IsRequired(true).HasForeignKey(l => l.ImageId);
            modelBuilder.Entity<ImageWidgetUsageLink>().HasOne(l => l.WidgetUsage).WithMany(u => u.ImageLinks).IsRequired(true).HasForeignKey(l => l.WidgetUsageId);
        }
    }

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

        public List<ImageWidgetUsageLink> UsedWidgets { get; set; }
    }

    public class ImageWidgetUsageLink
    {
        public Guid ImageId { get; set; }
        public WidgetImage Image { get; set; }
        public Guid WidgetUsageId { get; set; }
        public WidgetUsage WidgetUsage { get; set; }
    }

    public class WidgetUsage
    {
        public Guid Id { get; set; }
        public bool Retired { get; set; }

        public Bond Bond { get; set; }
        public Joint Joint { get; set; }
        public List<ImageWidgetUsageLink> ImageLinks { get; set; }
    }

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

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

query plan:

(QueryContext queryContext) => IEnumerable<<>f__AnonymousType1<IEnumerable<Guid>, IEnumerable<Guid>>> _InterceptExceptions(
    source: IEnumerable<<>f__AnonymousType1<IEnumerable<Guid>, IEnumerable<Guid>>> _Select(
        source: IEnumerable<ValueBuffer> _ShapedQuery(
            queryContext: queryContext, 
            shaperCommandContext: SelectExpression: 
                SELECT [i].[Id]
                FROM [WidgetImages] AS [i], 
            shaper: ValueBufferShaper), 
        selector: (ValueBuffer i) => new <>f__AnonymousType1<IEnumerable<Guid>, IEnumerable<Guid>>(
            IEnumerable<Guid> _Select(
                source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _InjectParameters(
                    queryContext: queryContext, 
                    source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _ShapedQuery(
                        queryContext: queryContext, 
                        shaperCommandContext: SelectExpression: 
                            SELECT DISTINCT [ub.WidgetUsage.Bond].[Id]
                            FROM [ImageWidgetUsageLinks] AS [ub]
                            INNER JOIN [WidgetUsages] AS [ub.WidgetUsage] ON [ub].[WidgetUsageId] = [ub.WidgetUsage].[Id]
                            LEFT JOIN [Bonds] AS [ub.WidgetUsage.Bond] ON [ub.WidgetUsage].[BondId] = [ub.WidgetUsage.Bond].[Id]
                            WHERE ([ub.WidgetUsage].[Retired] = 1) AND (@_outer_Id = [ub].[ImageId]), 
                        shaper: TypedCompositeShaper<TypedCompositeShaper<ValueBufferShaper, ValueBuffer, ValueBufferShaper, ValueBuffer, TransparentIdentifier<ValueBuffer, ValueBuffer>>, TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBufferShaper, ValueBuffer, TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>>), 
                    parameterNames: new string[]{ "_outer_Id" }, 
                    parameterValues: new object[]{ (object) Nullable<Guid> TryReadValue(i, 0, WidgetImage.Id) }), 
                selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t2) => (Guid) object t2.Outer.Outer.get_Item(0)), 
            IEnumerable<Guid> Distinct(IEnumerable<Guid> _Select(
                    source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _Where(
                        source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _InjectParameters(
                            queryContext: queryContext, 
                            source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _Where(
                                source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>> _ShapedQuery(
                                    queryContext: queryContext, 
                                    shaperCommandContext: SelectExpression: 
                                        SELECT [ub0].[ImageId], [ub.WidgetUsage.Joint].[Id]
                                        FROM [ImageWidgetUsageLinks] AS [ub0]
                                        INNER JOIN [WidgetUsages] AS [ub.WidgetUsage0] ON [ub0].[WidgetUsageId] = [ub.WidgetUsage0].[Id]
                                        LEFT JOIN [Joints] AS [ub.WidgetUsage.Joint] ON [ub.WidgetUsage0].[JointId] = [ub.WidgetUsage.Joint].[Id]
                                        WHERE @_outer_Id1 = [ub0].[ImageId], 
                                    shaper: TypedCompositeShaper<TypedCompositeShaper<ValueBufferShaper, ValueBuffer, ValueBufferShaper, ValueBuffer, TransparentIdentifier<ValueBuffer, ValueBuffer>>, TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBufferShaper, ValueBuffer, TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>>), 
                                predicate: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t2) =>  ---> [ub.WidgetUsage] <--- .Retired), 
                            parameterNames: new string[]{ "_outer_Id1" }, 
                            parameterValues: new object[]{ (object) Nullable<Guid> TryReadValue(i, 0, WidgetImage.Id) }), 
                        predicate: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t2) => Guid TryReadValue(i, 0, WidgetImage.Id) == Guid TryReadValue(t2.Outer.Outer, 0, ImageWidgetUsageLink.ImageId)), 
                    selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer> t2) => (Guid) object t2.Outer.Outer.get_Item(1)))
        )), 
    contextType: Repro6938.MyContext, 
    logger: InterceptingLogger<Query>, 
    queryContext: queryContext)

@ajcvickers ajcvickers modified the milestones: 2.0.0-preview1, 2.0.0 Apr 19, 2017
@maumar maumar modified the milestones: Backlog, 2.0.0 Jul 5, 2017
@ajcvickers ajcvickers modified the milestones: 2.1.0, Backlog Sep 7, 2017
@ajcvickers ajcvickers modified the milestones: 2.1.0-preview1, 2.1.0 Jan 17, 2018
@divega divega modified the milestones: 2.1.0-preview2, 2.1.0 Apr 2, 2018
@ajcvickers ajcvickers modified the milestones: 2.1.0, Backlog Apr 10, 2018
@ajcvickers ajcvickers changed the title ArgumentException when chaining projections (best guess) Query: ArgumentException when chaining projections (best guess) May 16, 2018
@smitpatel
Copy link
Contributor

This works in 3.1 release. Generated SQL

      SELECT [w].[Id], [t].[Id], [t].[ImageId], [t].[WidgetUsageId], [t].[Id0], [t0].[Id], [t0].[ImageId], [t0].[WidgetUsageId], [t0].[Id0]
      FROM [WidgetImages] AS [w]
      LEFT JOIN (
          SELECT DISTINCT [b].[Id], [i].[ImageId], [i].[WidgetUsageId], [w0].[Id] AS [Id0]
          FROM [ImageWidgetUsageLinks] AS [i]
          INNER JOIN [WidgetUsages] AS [w0] ON [i].[WidgetUsageId] = [w0].[Id]
          LEFT JOIN [Bonds] AS [b] ON [w0].[BondId] = [b].[Id]
          WHERE [w0].[Retired] = CAST(1 AS bit)
      ) AS [t] ON [w].[Id] = [t].[ImageId]
      LEFT JOIN (
          SELECT DISTINCT [j].[Id], [i0].[ImageId], [i0].[WidgetUsageId], [w1].[Id] AS [Id0]
          FROM [ImageWidgetUsageLinks] AS [i0]
          INNER JOIN [WidgetUsages] AS [w1] ON [i0].[WidgetUsageId] = [w1].[Id]
          LEFT JOIN [Joints] AS [j] ON [w1].[JointId] = [j].[Id]
          WHERE [w1].[Retired] = CAST(1 AS bit)
      ) AS [t0] ON [w].[Id] = [t0].[ImageId]
      ORDER BY [w].[Id], [t].[ImageId], [t].[WidgetUsageId], [t].[Id0], [t0].[ImageId], [t0].[WidgetUsageId], [t0].[Id0]

Single query fixed this.

@smitpatel smitpatel modified the milestones: Backlog, 3.1.0 Nov 21, 2019
@smitpatel smitpatel added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed try-on-latest labels Nov 21, 2019
@smitpatel smitpatel assigned smitpatel and unassigned maumar Nov 21, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. punted-for-2.0 punted-for-2.1 type-bug
Projects
None yet
Development

No branches or pull requests

7 participants