Problems with date format on queries

[arthuraugsten]

File a bug

I'm facing this issue and I didn't find a solution for this case. I'm developing a system with .NET 5, EF Core 5.0 and SQL Server. There's an entity called Post with a property named SubmittedDate with Datetime? type. On database it's stored with time, but I have a feature that need to filter all Posts from a specific date (Ex.: 2020/12/2), I'm filtering using the following code, but EF Core pass the parameters to query in a wrong order. It change the order of day and month. I'm using correct parameters on EF.Functions.DateFromParts(...).

Are there anything about it? I didn't find nothing and doesn't have a solution. May you help me?

Include your code

var date = new DateTime(2020, 12, 2);
var context = new PostContext();
	
await context.Set<Post>()
        .Where(t => t.SubmittedDate.Value.Date == EF.Functions.DateFromParts(date.Year, date.Month, date.Day))
        .CountAsync();

Include verbose output

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[@__date_Year_1='2020', @__date_Month_2='2', @__date_Day_3='12'], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM [Post] AS [c]
      WHERE (CONVERT(date, [c].[SubmittedDate]) = DATEFROMPARTS(@__date_Year_1, @__date_Month_2, @__date_Day_3))

Include provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 5.0
Operating system: Windows
IDE: Visual Studio Code 1.51.1

[ajcvickers]

@arthuraugsten I have not been able to reproduce this--see my code below. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

public class Post
{
    public int Id { get; set; }
    public DateTime? SubmittedDate { get; set; }
}

public class SomeDbContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().SetMinimumLevel(LogLevel.Information));

    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .UseLoggerFactory(ContextLoggerFactory)
            .EnableSensitiveDataLogging();
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var date = new DateTime(2020, 12, 2);

            Console.WriteLine(
                context.Set<Post>()
                .Where(t => t.SubmittedDate.Value.Date == EF.Functions.DateFromParts(date.Year, date.Month, date.Day))
                .Count());
        }
    }
}

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (16ms) [Parameters=[@__date_Year_1='2020', @__date_Month_2='12', @__date_Day_3='2'], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM [Posts] AS [p]
      WHERE CONVERT(date, [p].[SubmittedDate]) = DATEFROMPARTS(@__date_Year_1, @__date_Month_2, @__date_Day_3)

[arthuraugsten]

@ajcvickers I was analyzing my project's code to create a sample and I noticied my problem.

This problem ocurred on my Integration Tests. When I was building my URI with query params to send to my TestServer, I'm using ToString() on date, but my SO language is "pt-BR", so it was casted to 12/03/2020 (12 march 2020), a valid date on pt-br format! My API has en-US language defined, on model binding on route, the api received a invalid format for the language and changed the params on datetime.

To fix it I forced a culture info expected on my API.

Thank you for your attention and sorry for it! :)