Efcore: Cast to start of month on EF Core 3.1 exception -though worked on EF Core 2.1

[vlapenkov]

I need to use CAST in queries on SQL side, please any suggestions

var query = (await _db.CashPaymentDocsFrom1S.

                .Select(payment => new PaymentDto

                {

                    PeriodDate = payment.DocPeriod == null ?

                    new DateTime(payment.PayDate.Year, payment.PayDate.Month, 1) :

                    new DateTime(payment.DocPeriod.Value.Year, payment.DocPeriod.Value.Month,1)

                })

System.InvalidOperationException: The LINQ expression 'DbSet

  .Where(c => False || c.DocPeriod == null ? new DateTime(

    c.PayDate.Year,

    c.PayDate.Month,

    1

) : new DateTime(

    c.DocPeriod.Value.Year,

    c.DocPeriod.Value.Month,

   1

) >= __sDate_1 && (Nullable<DateTime>)c.DocPeriod == null ? new DateTime(

    c.PayDate.Year,

    c.PayDate.Month,

    1

) : new DateTime(

    c.DocPeriod.Value.Year,

    c.DocPeriod.Value.Month,

    1

) <= __eDate_2)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Further technical details

EF Core version: 3.1.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: WINDOWS 10
IDE: (e.g. Visual Studio 2019 16.3)

[ErikEJ]

If it worked with 2.1, maybe it was never translated to SQL, but evaluated on the client?

[vlapenkov]

If it worked with 2.1, maybe it was never translated to SQL, but evaluated on the client?

Probably, can i implement this in EF Core 3.1 ?

[ErikEJ]

Yes, I guess you could do something like:

var query = (await _db.CashPaymentDocsFrom1S.
                 .AsEnumerable()
                .Select(payment => new PaymentDto
                {
                    PeriodDate = payment.DocPeriod == null ?
                    new DateTime(payment.PayDate.Year, payment.PayDate.Month, 1) :
                    new DateTime(payment.DocPeriod.Value.Year, payment.DocPeriod.Value.Month,1)
                })

[vlapenkov]

as i understand AsEnumerable is lazy so it cause run query each time when iterating on collection? Should i use ToList to get all data once?

[vlapenkov]

when i use AsEnumerable -query doesn't goes to log file versus ToList, why so?

[smitpatel]

With AsEnumerable query will log only when you actually iterate enumerable. With ToList it will iterate right away hence generating log.