Translate to PostgreSQL date_trunc

[rlightner]

trafficstars

date_trunc('week', now()::date

Looking to make a call similar to this, and can't figure out what should be used now. I've also looked at EF.Property<DateTime>() but that doesn't seem to help.

[roji]

The provider does translate certain expressions with date_trunc (e.g. DateTime.Date gets translated to date_trunc('day', ...), but there's no general .NET method which is the equivalent of PostgreSQL date_trunc. We could add a translation for this under EF.Functions.

In the meantime you can map it via your own DbFunction mapping:

class Program
{
    static async Task Main(string[] args)
    {
        await using var ctx = new BlogContext();
        await ctx.Database.EnsureDeletedAsync();
        await ctx.Database.EnsureCreatedAsync();

        _ = ctx.Blogs.Where(b => BlogContext.DateTrunc("weeks", b.Creation) == new DateTime(2000, 1, 1)).ToList();
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(DateTruncMethod).HasName("date_trunc");
    }

    private static readonly MethodInfo DateTruncMethod
        = typeof(BlogContext).GetRuntimeMethod(nameof(DateTrunc), new[] { typeof(string), typeof(DateTime) });

    public static DateTime DateTrunc(string field, DateTime source)
        => throw new NotSupportedException();
}

public class Blog
{
    public int Id { get; set; }
    public DateTime Creation { get; set; }
}

[rlightner]

This worked perfectly! Would be really nice to have as a first class citizen in EF.Functions but this works for my case. Thanks again!

[kostazol]

When I try to use it, I got error 6.0.7 pg 14.4

 Npgsql.PostgresException (0x80004005): 42883: function public.date_trunc(unknown, timestamp without time zone) does not exist

      POSITION: 8
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Rosagromarket.Services.Orders.Managers.StatisticsManager.GetOrderStatisticsAsync(OrdersStatisticsFilter filter, Dictionary`2 stepTypes) in C:\Users\kostaz\Documents\1_git_repositories\marketplace-backend\Rosagromarket.Services.Orders\Managers\StatisticsManager.cs:line 185
         at Rosagromarket.Services.Orders.Controllers.StatisticsController.GetLastOrderedProductIdsForBuyerAsync(Guid buyerid, Int32 resultCount) in C:\Users\kostaz\Documents\1_git_repositories\marketplace-backend\Rosagromarket.Services.Orders\Controllers\StatisticsController.cs:line 65
         at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
         at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
         at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
        Exception data:
          Severity: ERROR
          SqlState: 42883
          MessageText: function public.date_trunc(unknown, timestamp without time zone) does not exist
          Hint: No function matches the given name and argument types. You might need to add explicit type casts.
          Position: 8
          File: parse_func.c
          Line: 629
          Routine: ParseFuncOrColumn

[roji]

@kostazol see below for a code sample that works for me on 6.0. Please post a similar minimal, runnable code sample which fails, or simply tweak the below to make it do so.

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = ctx.Blogs.Where(b => BlogContext.DateTrunc("weeks", b.Creation) == new DateTime(2000, 1, 1, 0, 0, 0, DateTimeKind.Utc)).ToList();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(DateTruncMethod).HasName("date_trunc");
    }

    private static readonly MethodInfo DateTruncMethod
        = typeof(BlogContext).GetRuntimeMethod(nameof(DateTrunc), new[] { typeof(string), typeof(DateTime) });

    public static DateTime DateTrunc(string field, DateTime source)
        => throw new NotSupportedException();
}

public class Blog
{
    public int Id { get; set; }
    public DateTime Creation { get; set; }
}

[FossaMalaEnt]

I'm using it on a ".GroupBy()" and it don't work.

public class MyContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    public MyContext (DbContextOptions<MyContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(DateTruncMethod).HasName("date_trunc");

        modelBuilder.Entity<MyEntity>().ToTable("TableName");
    }
    
    private static readonly MethodInfo DateTruncMethod
        = typeof(MyContext).GetRuntimeMethod(nameof(DateTrunc), new[] { typeof(string), typeof(DateTime) });

    public static DateTime DateTrunc(string field, DateTime source)
        => throw new NotSupportedException();
}

LINQ

        var q = this_context.MyEntities.Where(x => x.mac.Equals("address"));
        var qualcosa = await q.GroupBy(x => MyContext.DateTrunc("minute", x.Created))
                                 .Select(x => new ResultDTO()
                                 {
                                     Date = x.Key.ToString(),
                                     Average = x.Average(y => y.Value)
                                 })
                                 .ToListAsync();

But i get

The LINQ expression 'DbSet<MyEntity>() .Where(x => x.Mac.Equals("address")) .GroupBy(x => MyContext.DateTrunc(field: "hour", source: x.Created))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

It fails on the groupBy, I know cause I've tried commenting the Select and fails anyway, but if I comment also the groupBy then it works.

[Bouke]

 Npgsql.PostgresException (0x80004005): 42883: function public.date_trunc(unknown, timestamp without time zone) does not exist

I had the same problem, and I had specified a default schema like this:

modelBuilder.HasDefaultSchema("public");

The function doesn't have a schema, so update the registration to specify the schema:

modelBuilder.HasDbFunction(DateTruncMethod).HasName("date_trunc").HasSchema("");
//                                                               ^^^^^^^^^^^^^^

[roji]

@Bouke IsBuiltIn() would be the more correct way to do this, give that a try:

modelBuilder.HasDbFunction(DateTruncMethod).HasName("date_trunc").IsBuiltIn();