EF Core CreateDateTime could not be translated

Question

I'm trying to run a linq to entities query in EF Core 6, but I'm receiving an exception saying that System.Data.Entity.DbFunctions.CreateDateTime could not be translated.

My goal is to filter a collection by initial and final month and year.

Im trying that:

var initialDate = new DateTime(request.InitialYear, request.InitialMonth, 1);
var finalDate = new DateTime(request.FinalYear,request.FinalMonth, 1);

query = query.where(q => System.Data.Entity.DbFunctions.CreateDateTime(q.Year,q.Month, 1, 0, 0, 0) >= initialDate && System.Data.Entity.DbFunctions.CreateDateTime(q.Year,q.Month, 1, 0, 0, 0) <= finalDate);

Any sugestions?

Answer 1

System.Data.Entity is not part of Entity Framework Core, so its classes and methods may not be translated by EF. I did a quick search and found EF.Functions.DateFromParts in package Microsoft.EntityFrameworkCore.SqlServer. It means that query translation heavily depends on database provider. You have not specified one in your question.

Usage:

EF.Functions.DateFromParts(q.Year, q.Month, 1) >= initialDate