EF5 to EF6 getting 'Unable to translate set operation when matching columns on both sides have different store types.'

See original GitHub issue

EF Core version: 6.08 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Win 10 IDE: VS 17.3.2

After migrating from EF5 to EF6 and and .NET 5 to .NET 6 we get above error from this query

        public async Task<IEnumerable<ReconcilationSummaryData>> Handle(GetReconciliationSummaryQuery query)
        {
            return await _ctx.Set<PaymentTransaction>()
                .Where(pt => !pt.Balanced && pt.PaymentState == PaymentState.PaymentComplete)
                .Select(pt => new
                {
                    Amount = pt.PaymentDirection == PaymentDirection.Incoming ? pt.Amount : -pt.Amount,
                    pt.PaymentCategory,
                    pt.ReferredAccount.AccountNumber,
                    pt.ReferredAccount.AccountName,
                    pt.ReferredAccount.BgcCustomer.ExternalSystem
                })
                .Concat(_ctx.Set<PaymentTransactionAggregation>()
                    .Select(pta => new
                {
                    pta.Amount,
                    pta.PaymentCategory,
                    pta.ReferredAccount.AccountNumber,
                    pta.ReferredAccount.AccountName,
                    pta.ReferredAccount.BgcCustomer.ExternalSystem
                }))
                .GroupBy(pt => new {pt.PaymentCategory, pt.AccountNumber, pt.AccountName, pt.ExternalSystem })
                .Select(g => new ReconcilationSummaryData
                {
                    AccountNumber = g.Key.AccountNumber,
                    AccountName = g.Key.AccountName,
                    Category = (TransactionCategory) g.Key.PaymentCategory,
                    ServiceType = (ServiceTypeEnum) g.Key.ExternalSystem,
                    Amount = g.Sum(pt => pt.Amount)
                })
                .ToListAsync();
        }

Issue Analytics

State:
Created a year ago
Comments:8 (2 by maintainers)

Top GitHub Comments

1reaction

ajcvickerscommented, Aug 30, 2022

@AndersMalmgren Configuration of PaymentTransaction.Amount is invalid:

builder.Property(p => p.Amount)
    .HasColumnType("decimal(18,2")
    .IsRequired();

Correcting it to:

builder.Property(p => p.Amount)
    .HasColumnType("decimal(18,2)")
    .IsRequired();

fixes the error in the given repro.

0reactions

AndersMalmgrencommented, Sep 4, 2022

@AndersMalmgren consider using the strongly typed HasPrecision(int precision, int scale) to prevent this issue from occuring.
 builder.Property(p => p.Amount)
.HasPrecision(18, 2)
 .IsRequired();
Or use the attribute [Precision(18, 2)]

Good point, thanks!

Top Results From Across the Web

Union with EF Core returns unable to translate set ...

The type issue was with UserName columns, which are varchars. ... operation when matching columns on both sides have different store types.

Unable to translate set operation when matching columns ...

InvalidOperationException : Unable to translate set operation when matching columns on both sides have different store types. at Microsoft.

Unable to translate set operation when matching columns ...

it is not work,and throw out：Unable to translate set operation when matching columns on both sides have different store types.

Union with EF Core returns unable to translate set operation ...

[Solved]-Union with EF Core returns unable to translate set operation ... set operation when matching columns on both sides have different store types.":...

Writing Better Performing Queries with LINQ on EF Core 6.0 ⚙️

⬆️ Enhancements with EF Core 6.0 Specifically, EF Core now: Translate GroupBy followed by FirstOrDefault (or similar) over a group. Supports selecting the...