Efcore: Exponential hash code calculation in ReplacingExpressionVisitor

Created on 29 Jan 2020  路  14Comments  路  Source: dotnet/efcore

I have query like this, result1 success get data but result2 stuck(not throw exception and not get result).

``` C#
var trx = context.Adjustments.Select(x => new TransactionRef
{
Id = x.Id,
Source = x.IsPositive ? UserActionSource.AdjustmentPlus : UserActionSource.AdjustmentMinus,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = x.CostCenterId
}).Union(context.SupplierDownPayments.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.SupplierDownPayment,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.PurchaseRequisitions.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.PurchaseRequisition,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.PurchaseOrders.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.PurchaseOrder,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.ReceivedItems.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.ReceivedItem,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
}))
.Union(context.Purchases.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.Purchase,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.PurchaseReceivings.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.PurchaseReceiving,
RefCode = x.Code,
BranchId = null,
CostCenterId = null
})).Union(context.PurchaseReturns.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.PurchaseReturn,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.JournalVouchers.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.JournalVoucher,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.OtherDeposits.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.OtherDeposit,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.OtherPayments.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.OtherPayment,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.WarehouseTransfers.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.WarehouseTransfer,
RefCode = x.Code,
BranchId = null,
CostCenterId = null
})).Union(context.ItemTransferIns.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.ItemTransferIn,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
})).Union(context.ItemTransferOuts.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.ItemTransferOut,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
}));

        var result1= await (from i in trx
                  select new
                  {
                      i.Id
                  }).ToListAsync(cancellationToken);

        var result2 = await (from i in trx
                  select new
                  {
                      i.Id,
                      IsA = (from ah in context.AccountHistories where i.Id == ah.RefId select ah.Id).Count() > 0
                  }).ToListAsync(cancellationToken);

```
how to fix it?

Further technical details

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.4

Servicing-approved area-perf closed-fixed customer-reported type-bug
Source
picture suriyadi15

All 14 comments

result2 has the result if the trx use 2 Union

``` C#
var trx = context.Adjustments.Select(x => new TransactionRef
{
Id = x.Id,
Source = x.IsPositive ? UserActionSource.AdjustmentPlus : UserActionSource.AdjustmentMinus,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = x.CostCenterId
}).Union(context.SupplierDownPayments.Select(x => new TransactionRef
{
Id = x.Id,
Source = UserActionSource.SupplierDownPayment,
RefCode = x.Code,
BranchId = x.BranchId,
CostCenterId = null
}));

var result2 = await (from i in trx
select new
{
i.Id,
IsA = (from ah in context.AccountHistories where i.Id == ah.RefId select ah.Id).Count() > 0
}).ToListAsync(cancellationToken);
```

suriyadi15 picture suriyadi15 on 29 Jan 2020

@suriyadi15 Can you look in the logs and post the SQL generated from the query?

ajcvickers picture ajcvickers on 29 Jan 2020

@suriyadi15 also please post code for all your entities and the contents of OnModelCreating method in the DbContext

maumar picture maumar on 29 Jan 2020

@suriyadi15 Can you look in the logs and post the SQL generated from the query?

sql generated from result2 is nothing, when i debug and break point in result2 and press f10, nothing response and don't throw exception. my request from browser is still pending.

The thread 0x3d2c has exited with code 0 (0x0).
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t11].[Id]
FROM (
    SELECT [a].[Id], CASE
        WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
        ELSE 38
    END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
    FROM [Adjustments] AS [a]
    UNION
    SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
    FROM [SupplierDownPayments] AS [s]
    UNION
    SELECT [p].[Id], 29 AS [Source], [p].[Code] AS [RefCode], [p].[BranchId], NULL AS [CostCenterId]
    FROM [PurchaseRequisitions] AS [p]
    UNION
    SELECT [p0].[Id], 30 AS [Source], [p0].[Code] AS [RefCode], [p0].[BranchId], NULL AS [CostCenterId]
    FROM [PurchaseOrders] AS [p0]
    UNION
    SELECT [r].[Id], 31 AS [Source], [r].[Code] AS [RefCode], [r].[BranchId], NULL AS [CostCenterId]
    FROM [ReceivedItems] AS [r]
    UNION
    SELECT [p1].[Id], 33 AS [Source], [p1].[Code] AS [RefCode], [p1].[BranchId], NULL AS [CostCenterId]
    FROM [Purchases] AS [p1]
    UNION
    SELECT [p2].[Id], 37 AS [Source], [p2].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
    FROM [PurchaseReceivings] AS [p2]
    UNION
    SELECT [p3].[Id], 42 AS [Source], [p3].[Code] AS [RefCode], [p3].[BranchId], NULL AS [CostCenterId]
    FROM [PurchaseReturns] AS [p3]
    UNION
    SELECT [j].[Id], 28 AS [Source], [j].[Code] AS [RefCode], [j].[BranchId], NULL AS [CostCenterId]
    FROM [JournalVouchers] AS [j]
    UNION
    SELECT [o].[Id], 35 AS [Source], [o].[Code] AS [RefCode], [o].[BranchId], NULL AS [CostCenterId]
    FROM [OtherDeposits] AS [o]
    UNION
    SELECT [o0].[Id], 34 AS [Source], [o0].[Code] AS [RefCode], [o0].[BranchId], NULL AS [CostCenterId]
    FROM [OtherPayments] AS [o0]
    UNION
    SELECT [w].[Id], 39 AS [Source], [w].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
    FROM [WarehouseTransfers] AS [w]
    UNION
    SELECT [i].[Id], 41 AS [Source], [i].[Code] AS [RefCode], [i].[BranchId], NULL AS [CostCenterId]
    FROM [ItemTransferIns] AS [i]
    UNION
    SELECT [i0].[Id], 40 AS [Source], [i0].[Code] AS [RefCode], [i0].[BranchId], NULL AS [CostCenterId]
    FROM [ItemTransferOuts] AS [i0]
) AS [t11]
The thread 0x27e4 has exited with code 0 (0x0).
The thread 0x2eb8 has exited with code 0 (0x0).
The thread 0x22a0 has exited with code 0 (0x0).
The thread 0x3054 has exited with code 0 (0x0).
The thread 0x4030 has exited with code 0 (0x0).
The thread 0x26b8 has exited with code 0 (0x0).
suriyadi15 picture suriyadi15 on 30 Jan 2020

@suriyadi15 also please post code for all your entities and the contents of OnModelCreating method in the DbContext

This is one sample entity & configuration code

``` C#
public class Adjustment : IEntityKey, IEntityOwner, ITransactionRef, IOtherColumn
{
public Adjustment()
{
AdjustmentDetails = new HashSet();
AdjustmentFiles = new HashSet();
}

    public Guid Id { get; set; }
    public Guid CompanyId { get; set; }
    public string Code { get; set; }
    public DateTime DateTime { get; set; }

    public bool IsPositive { get; set; }
    public Guid AccountId { get; set; }
    public string Description { get; set; }

    public Guid BranchId { get; set; }
    public Guid? CostCenterId { get; set; }

    public bool HasValidated { get; set; }
    public Guid? ValidatedBy { get; set; }
    public DateTime? ValidatedOn { get; set; }

    public Guid CreatedById { get; set; }
    public DateTime CreatedOn { get; set; }
    public CompanyUser CreatedBy { get; set; }
    public Guid? UpdatedById { get; set; }
    public DateTime? UpdatedOn { get; set; }
    public CompanyUser UpdatedBy { get; set; }

    public Account Account { get; set; }
    public Branch Branch { get; set; }
    public CostCenter CostCenter { get; set; }

    public ICollection<AdjustmentDetail> AdjustmentDetails { get; private set; }
    public ICollection<AdjustmentFile> AdjustmentFiles { get; private set; }

    public string Ref1 { get; set; }
    public string Ref2 { get; set; }
    public string Ref3 { get; set; }
    public string OtherColumn1 { get; set; }
    public string OtherColumn2 { get; set; }
    public string OtherColumn3 { get; set; }
    public string OtherColumn4 { get; set; }
    public string OtherColumn5 { get; set; }
    public string OtherColumn6 { get; set; }
    public string OtherColumn7 { get; set; }
    public string OtherColumn8 { get; set; }
    public string OtherColumn9 { get; set; }
    public string OtherColumn10 { get; set; }
}


``` C#
public class AdjustmentConfiguration : IEntityTypeConfiguration<Adjustment>
    {
        public void Configure(EntityTypeBuilder<Adjustment> builder)
        {
            builder.ConfigureKey()
                .ConfigureOwner(nameof(Adjustment.CompanyId))
                .ConfigureTransactionRef()
                .ConfigureOtherColumn();

            builder.HasIndex(x => new { x.Code, x.CompanyId })
                .IsUnique();

            builder.Property(x => x.Code)
                .IsRequired();

            builder.Property(x => x.CompanyId)
                .IsRequired();
            builder.HasOne<Company>()
                .WithMany()
                .HasForeignKey(x => x.CompanyId)
                .OnDelete(DeleteBehavior.Cascade);

            builder.Property(x => x.DateTime)
                .IsRequired();

            builder.Property(x => x.IsPositive)
                .IsRequired();

            builder.Property(x => x.AccountId)
               .IsRequired();
            builder.HasOne(x=>x.Account)
                .WithMany()
                .HasForeignKey(x => x.AccountId)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Property(x => x.Description)
                .IsRequired(false);

            builder.Property(x => x.BranchId)
                .IsRequired();
            builder.HasOne(x=>x.Branch)
                .WithMany()
                .HasForeignKey(x => x.BranchId)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Property(x => x.CostCenterId)
                .IsRequired(false);
            builder.HasOne(x=>x.CostCenter)
                .WithMany()
                .HasForeignKey(x => x.CostCenterId)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Property(x => x.HasValidated)
                .IsRequired();

            builder.Property(x => x.ValidatedBy)
                .IsRequired(false);
            builder.HasOne<CompanyUser>()
                .WithMany()
                .HasForeignKey(x => new { x.ValidatedBy, x.CompanyId })
                .OnDelete(DeleteBehavior.Restrict);

            builder.Property(x => x.ValidatedOn)
                .IsRequired(false);
        }
    }

``` C#
public static EntityTypeBuilder ConfigureKey(this EntityTypeBuilder builder) where TEntity : class, IEntityKey
{
builder.HasKey(u => u.Id);
builder.Property(u => u.Id)
.HasDefaultValueSql("NEWID()");

        return builder;
    }

public static EntityTypeBuilder ConfigureTransactionRef(this EntityTypeBuilder builder) where TEntity : class, ITransactionRef
{
builder.Property(x => x.Ref1)
.IsRequired(false);

        builder.Property(x => x.Ref2)
            .IsRequired(false);

        builder.Property(x => x.Ref3)
            .IsRequired(false);

        return builder;
    }

public static EntityTypeBuilder ConfigureOtherColumn(this EntityTypeBuilder builder) where TEntity : class, IOtherColumn
{
builder.Property(x => x.OtherColumn1)
.IsRequired(false);

        builder.Property(x => x.OtherColumn2)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn3)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn4)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn5)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn6)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn7)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn8)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn9)
            .IsRequired(false);

        builder.Property(x => x.OtherColumn10)
            .IsRequired(false);

        return builder;
    }

```

suriyadi15 picture suriyadi15 on 30 Jan 2020

@roji Thoughts?

ajcvickers picture ajcvickers on 30 Jan 2020

The big LINQ query with all the Unions is assigned to variable trx which is not evaluated/executed (there is no ToList or anything). Then, the two other queries producing result1 and result2 query from something called a which isn't shown.

Is it possible to have the runnable code actually being executed? I'm afraid there's something missing in your sample which could explain the issue.

roji picture roji on 30 Jan 2020
👍1

I apologize for the writing error above. I have fixed the code above, the variable of a should be trx.
should be from i in trx

suriyadi15 picture suriyadi15 on 31 Jan 2020

@suriyadi15 thanks. The SQL produced for result1 seems correct, could you please set your logging level to Debug and see if an SQL query comes out for result2 as well? The log event with the SQL you're seeing is "Executed DbCommand", which is logged on level Information (the default) and which only occurs after the query has been executed. In your situation, if EF Core is sending SQL to the database that causes the database to block, you wouldn't see any SQL logged unless you enable Debug for the "Executing DbCommand" event.

I don't have enough information yet to know exactly what's going on, but I suspect the second query simply is taking too long to execute for the database. This could be because of a massive amount of data in AccountHistories, or missing indexes that would help the query run more quickly. So I'd suggest trying to run the same program against an empty development/preproduction database to see the results. If there really is something wrong with EF Core, the query should also hang there; otherwise it should execute quickly, pointing more to a data/indexes/database-side problem.

roji picture roji on 31 Jan 2020
👍1

I have tried to set Logging level to Debug and empty database. but i don't see logging for result2

```C#
// Add DbContext using SQL Server Provider
services.AddDbContext(options =>
{
options.UseSqlServer(Configuration.GetConnectionString("ConnString"));
options.UseExceptionProcessor();
if (env.IsDevelopment())
{
options.EnableSensitiveDataLogging();
options.UseLoggerFactory(LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => true).AddDebug();
}));
}
});


here is the output debug:

The thread 0x7dc0 has exited with code 0 (0x0).
The thread 0x356c has exited with code 0 (0x0).
Microsoft.EntityFrameworkCore.Query: Debug: queryContext => new QueryingEnumerable<<>f__AnonymousType120>(
(RelationalQueryContext)queryContext,
RelationalCommandCache,
null,
null,
Func>,
Bis.Persistence.MyDbContext,
DiagnosticsLogger
)
Microsoft.EntityFrameworkCore.Database.Command: Debug: Creating DbCommand for 'ExecuteReader'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Created DbCommand for 'ExecuteReader' (1ms).
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t11].[Id]
FROM (
SELECT [a].[Id], CASE
WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
FROM [Adjustments] AS [a]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
UNION
SELECT [p].[Id], 29 AS [Source], [p].[Code] AS [RefCode], [p].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseRequisitions] AS [p]
UNION
SELECT [p0].[Id], 30 AS [Source], [p0].[Code] AS [RefCode], [p0].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseOrders] AS [p0]
UNION
SELECT [r].[Id], 31 AS [Source], [r].[Code] AS [RefCode], [r].[BranchId], NULL AS [CostCenterId]
FROM [ReceivedItems] AS [r]
UNION
SELECT [p1].[Id], 33 AS [Source], [p1].[Code] AS [RefCode], [p1].[BranchId], NULL AS [CostCenterId]
FROM [Purchases] AS [p1]
UNION
SELECT [p2].[Id], 37 AS [Source], [p2].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [PurchaseReceivings] AS [p2]
UNION
SELECT [p3].[Id], 42 AS [Source], [p3].[Code] AS [RefCode], [p3].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseReturns] AS [p3]
UNION
SELECT [j].[Id], 28 AS [Source], [j].[Code] AS [RefCode], [j].[BranchId], NULL AS [CostCenterId]
FROM [JournalVouchers] AS [j]
UNION
SELECT [o].[Id], 35 AS [Source], [o].[Code] AS [RefCode], [o].[BranchId], NULL AS [CostCenterId]
FROM [OtherDeposits] AS [o]
UNION
SELECT [o0].[Id], 34 AS [Source], [o0].[Code] AS [RefCode], [o0].[BranchId], NULL AS [CostCenterId]
FROM [OtherPayments] AS [o0]
UNION
SELECT [w].[Id], 39 AS [Source], [w].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [WarehouseTransfers] AS [w]
UNION
SELECT [i].[Id], 41 AS [Source], [i].[Code] AS [RefCode], [i].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferIns] AS [i]
UNION
SELECT [i0].[Id], 40 AS [Source], [i0].[Code] AS [RefCode], [i0].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferOuts] AS [i0]
) AS [t11]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t11].[Id]
FROM (
SELECT [a].[Id], CASE
WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
FROM [Adjustments] AS [a]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
UNION
SELECT [p].[Id], 29 AS [Source], [p].[Code] AS [RefCode], [p].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseRequisitions] AS [p]
UNION
SELECT [p0].[Id], 30 AS [Source], [p0].[Code] AS [RefCode], [p0].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseOrders] AS [p0]
UNION
SELECT [r].[Id], 31 AS [Source], [r].[Code] AS [RefCode], [r].[BranchId], NULL AS [CostCenterId]
FROM [ReceivedItems] AS [r]
UNION
SELECT [p1].[Id], 33 AS [Source], [p1].[Code] AS [RefCode], [p1].[BranchId], NULL AS [CostCenterId]
FROM [Purchases] AS [p1]
UNION
SELECT [p2].[Id], 37 AS [Source], [p2].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [PurchaseReceivings] AS [p2]
UNION
SELECT [p3].[Id], 42 AS [Source], [p3].[Code] AS [RefCode], [p3].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseReturns] AS [p3]
UNION
SELECT [j].[Id], 28 AS [Source], [j].[Code] AS [RefCode], [j].[BranchId], NULL AS [CostCenterId]
FROM [JournalVouchers] AS [j]
UNION
SELECT [o].[Id], 35 AS [Source], [o].[Code] AS [RefCode], [o].[BranchId], NULL AS [CostCenterId]
FROM [OtherDeposits] AS [o]
UNION
SELECT [o0].[Id], 34 AS [Source], [o0].[Code] AS [RefCode], [o0].[BranchId], NULL AS [CostCenterId]
FROM [OtherPayments] AS [o0]
UNION
SELECT [w].[Id], 39 AS [Source], [w].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [WarehouseTransfers] AS [w]
UNION
SELECT [i].[Id], 41 AS [Source], [i].[Code] AS [RefCode], [i].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferIns] AS [i]
UNION
SELECT [i0].[Id], 40 AS [Source], [i0].[Code] AS [RefCode], [i0].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferOuts] AS [i0]
) AS [t11]
Microsoft.EntityFrameworkCore.Database.Command: Debug: A data reader was disposed.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closing connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
The thread 0x8e60 has exited with code 0 (0x0).
The thread 0x4a30 has exited with code 0 (0x0).
The thread 0xa1c has exited with code 0 (0x0).
The thread 0x6d04 has exited with code 0 (0x0).
The thread 0x3c58 has exited with code 0 (0x0).
The thread 0xaac has exited with code 0 (0x0).
The thread 0xde8 has exited with code 0 (0x0).
The thread 0x59bc has exited with code 0 (0x0).
The thread 0x65ec has exited with code 0 (0x0).
The thread 0x3954 has exited with code 0 (0x0).


when i try running it on sql server, it runs normally, this is the query:

SELECT [t].[Id], CASE
WHEN (
SELECT COUNT(*)
FROM [AccountHistories] AS [a]
WHERE [t].[Id] = [a].[RefId]) > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [IsA]
FROM (
SELECT [a].[Id], CASE
WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
FROM [Adjustments] AS [a]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
UNION
SELECT [p].[Id], 29 AS [Source], [p].[Code] AS [RefCode], [p].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseRequisitions] AS [p]
UNION
SELECT [p0].[Id], 30 AS [Source], [p0].[Code] AS [RefCode], [p0].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseOrders] AS [p0]
UNION
SELECT [r].[Id], 31 AS [Source], [r].[Code] AS [RefCode], [r].[BranchId], NULL AS [CostCenterId]
FROM [ReceivedItems] AS [r]
UNION
SELECT [p1].[Id], 33 AS [Source], [p1].[Code] AS [RefCode], [p1].[BranchId], NULL AS [CostCenterId]
FROM [Purchases] AS [p1]
UNION
SELECT [p2].[Id], 37 AS [Source], [p2].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [PurchaseReceivings] AS [p2]
UNION
SELECT [p3].[Id], 42 AS [Source], [p3].[Code] AS [RefCode], [p3].[BranchId], NULL AS [CostCenterId]
FROM [PurchaseReturns] AS [p3]
UNION
SELECT [j].[Id], 28 AS [Source], [j].[Code] AS [RefCode], [j].[BranchId], NULL AS [CostCenterId]
FROM [JournalVouchers] AS [j]
UNION
SELECT [o].[Id], 35 AS [Source], [o].[Code] AS [RefCode], [o].[BranchId], NULL AS [CostCenterId]
FROM [OtherDeposits] AS [o]
UNION
SELECT [o0].[Id], 34 AS [Source], [o0].[Code] AS [RefCode], [o0].[BranchId], NULL AS [CostCenterId]
FROM [OtherPayments] AS [o0]
UNION
SELECT [w].[Id], 39 AS [Source], [w].[Code] AS [RefCode], NULL AS [BranchId], NULL AS [CostCenterId]
FROM [WarehouseTransfers] AS [w]
UNION
SELECT [i].[Id], 41 AS [Source], [i].[Code] AS [RefCode], [i].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferIns] AS [i]
UNION
SELECT [i0].[Id], 40 AS [Source], [i0].[Code] AS [RefCode], [i0].[BranchId], NULL AS [CostCenterId]
FROM [ItemTransferOuts] AS [i0]
) AS [t]

Expected results from 'result2' should be like the query above. Is there an error with my code?

This is the output debug if the `Union` is only one:

Microsoft.EntityFrameworkCore.Query: Debug: queryContext => new QueryingEnumerable<<>f__AnonymousType120>(
(RelationalQueryContext)queryContext,
RelationalCommandCache,
null,
null,
Func>,
Bis.Persistence.MyDbContext,
DiagnosticsLogger
)
Microsoft.EntityFrameworkCore.Database.Command: Debug: Creating DbCommand for 'ExecuteReader'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Created DbCommand for 'ExecuteReader' (1ms).
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id]
FROM (
SELECT [a].[Id], CASE
WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
FROM [Adjustments] AS [a]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
) AS [t]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id]
FROM (
SELECT [a].[Id], CASE
WHEN [a].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a].[Code] AS [RefCode], [a].[BranchId], [a].[CostCenterId]
FROM [Adjustments] AS [a]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
) AS [t]
Microsoft.EntityFrameworkCore.Database.Command: Debug: A data reader was disposed.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closing connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Query: Debug: queryContext => new QueryingEnumerable<<>f__AnonymousType121>(
(RelationalQueryContext)queryContext,
RelationalCommandCache,
null,
null,
Func>,
Bis.Persistence.MyDbContext,
DiagnosticsLogger
)
Microsoft.EntityFrameworkCore.Database.Command: Debug: Creating DbCommand for 'ExecuteReader'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Created DbCommand for 'ExecuteReader' (1ms).
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], CASE
WHEN (
SELECT COUNT()
FROM [AccountHistories] AS [a]
WHERE [t].[Id] = [a].[RefId]) > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [IsA]
FROM (
SELECT [a0].[Id], CASE
WHEN [a0].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a0].[Code] AS [RefCode], [a0].[BranchId], [a0].[CostCenterId]
FROM [Adjustments] AS [a0]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
) AS [t]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], CASE
WHEN (
SELECT COUNT()
FROM [AccountHistories] AS [a]
WHERE [t].[Id] = [a].[RefId]) > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [IsA]
FROM (
SELECT [a0].[Id], CASE
WHEN [a0].[IsPositive] = CAST(1 AS bit) THEN 36
ELSE 38
END AS [Source], [a0].[Code] AS [RefCode], [a0].[BranchId], [a0].[CostCenterId]
FROM [Adjustments] AS [a0]
UNION
SELECT [s].[Id], 24 AS [Source], [s].[Code] AS [RefCode], [s].[BranchId], NULL AS [CostCenterId]
FROM [SupplierDownPayments] AS [s]
) AS [t]
Microsoft.EntityFrameworkCore.Database.Command: Debug: A data reader was disposed.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closing connection to database 'Test' on server '(localdb)\mssqllocaldb'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
```

suriyadi15 picture suriyadi15 on 1 Feb 2020

Pinging @roji as requested.

ajcvickers picture ajcvickers on 8 Feb 2020

Repro'ed this, we seem to have a case of exponential hashcode calculation caused by the dictionary in ReplacingExpressionVisitor. Every time that visitor checks of a given node is in its replacements dictionary, this triggers recursive hashcode calculation for the entire subtree; then the visitor itself recurses inside the subnodes and recalculates again. The moment the tree gets somewhat deep this causes a huge amount of hashcode calculation.

A proposed fix in #19858 is for ReplacingExpressionVisitor to simply hold two arrays instead of a dictionary; this allows it to do much cheaper Equals checks (and is confirmed to resolve this issue).

Some other thoughts:

  • We could cache hashcodes for immutable node types (#19860)
  • We could stop exhaustively including all query sub-nodes in hash calculation (#19859).

Even with the above, I think #19858 (two arrays instead of a dictionary in ReplacingExpressionVisitor) is a good thing to do.

Runnable repro:


Click to expand

```c#
class Program
{
static async Task Main(string[] args)
{
using var context = new BlogContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

    var trx = context.Adjustments.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = x.IsPositive ? UserActionSource.AdjustmentPlus : UserActionSource.AdjustmentMinus,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = x.CostCenterId
    }).Union(context.SupplierDownPayments.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.SupplierDownPayment,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.PurchaseRequisitions.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.PurchaseRequisition,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.PurchaseOrders.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.PurchaseOrder,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.ReceivedItems.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.ReceivedItem,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    }))
    .Union(context.Purchases.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.Purchase,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.PurchaseReceivings.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.PurchaseReceiving,
        RefCode = x.Code,
        BranchId = null,
        CostCenterId = null
    })).Union(context.PurchaseReturns.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.PurchaseReturn,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.JournalVouchers.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.JournalVoucher,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.OtherDeposits.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.OtherDeposit,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.OtherPayments.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.OtherPayment,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    })).Union(context.WarehouseTransfers.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.WarehouseTransfer,
        RefCode = x.Code,
        BranchId = null,
        CostCenterId = null
    })).Union(context.ItemTransferIns.Select(x => new TransactionRef
    {
        Id = x.Id,
        Source = UserActionSource.ItemTransferIn,
        RefCode = x.Code,
        BranchId = x.BranchId,
        CostCenterId = null
    }));

    var result2 = await (from i in trx
        select new
        {
            i.Id,
            IsA = (from ah in context.AccountHistories where i.Id == ah.RefId select ah.Id).Count() > 0
        }).ToListAsync();
}

}

public class BlogContext : DbContext
{
public DbSet Adjustments { get; set; }
public DbSet SupplierDownPayments { get; set; }
public DbSet PurchaseRequisitions { get; set; }
public DbSet PurchaseOrders { get; set; }
public DbSet ReceivedItems { get; set; }
public DbSet Purchases { get; set; }
public DbSet PurchaseReceivings { get; set; }
public DbSet PurchaseReturns { get; set; }
public DbSet JournalVouchers { get; set; }
public DbSet OtherDeposits { get; set; }
public DbSet OtherPayments { get; set; }
public DbSet WarehouseTransfers { get; set; }
public DbSet ItemTransferIns { get; set; }
public DbSet ItemTransferOuts { get; set; }

public DbSet<AccountHistory> AccountHistories { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer("");

}

public class TransactionRef
{
public Guid Id { get; set; }
public UserActionSource Source { get; set; }
public string RefCode { get; set; }
public Guid? BranchId { get; set; }
public Guid? CostCenterId { get; set; }
}

public class AccountHistory
{
public Guid Id { get; set; }
public Guid RefId { get; set; }
}

public enum UserActionSource
{
AdjustmentPlus,
AdjustmentMinus,
SupplierDownPayment,
PurchaseRequisition,
PurchaseOrder,
ReceivedItem,
Purchase,
PurchaseReceiving,
PurchaseReturn,
JournalVoucher,
OtherDeposit,
OtherPayment,
WarehouseTransfer,
ItemTransferIn,
ItemTransferOut,
}

public class Adjustment
{
public Guid Id { get; set; }
public string Code { get; set; }
public bool IsPositive { get; set; }
public Guid BranchId { get; set; }
public Guid? CostCenterId { get; set; }
}

public class SupplierDownPayment
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class PurchaseRequisition
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class PurchaseOrder
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class ReceivedItem
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class Purchase
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class PurchaseReceiving
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class PurchaseReturn
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class JournalVoucher
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class OtherDeposit
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class OtherPayment
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class WarehouseTransfer
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class ItemTransferIn
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}

public class ItemTransferOut
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid? BranchId { get; set; }
}
```

roji picture roji on 10 Feb 2020

Reopening for servicing in 3.1

roji picture roji on 10 Feb 2020

Using #19867 to track the servicing fix.

roji picture roji on 10 Feb 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

iberodev picture iberodev  路  3Comments
bgribaudo picture bgribaudo  路  3Comments
divega picture divega  路  3Comments
TPJ11 picture TPJ11  路  3Comments
spottedmahn picture spottedmahn  路  3Comments