Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Npgsql in EFCore cannot translate DateTimeOffset.Date

Is this expected behavior? if so, is there any workaround? I'm using Net Core 5.0 and npgsql 5.0

    public async Task<List<long>> GetGuiaIdsWithFilters(DateTime createddate)
    {
        IQueryable<Guia> guiaToReturn = _context.Guia;
        createddate = createddate.ToUniversalTime();
        return await guiaToReturn.Where(g => g.StatusHistory.Any(s.Fecha.Date == createddate.Date)).Select(x => x.Id).ToListAsync();
    }

Running this query will return the following error which I isolated to the provider being unable to extract the Date part: s.Fecha.Date (DateTimeOffset.Date). I read the documentation and DateTimeOffset will be stored as timestamptz in PostgreSQL, but I didn't find any limitations on common functions such as getting the date part.

The LINQ expression [...] could not be translated. Either rewrite the query in a form that can be translated

I can do the following workaround which works fine but it seems to be a much more inefficient query:

    public async Task<List<long>> GetGuiaIdsWithFilters(DateTime createddate)
    {
        IQueryable<Guia> guiaToReturn = _context.Guia;
        var FromDate = createddate.ToUniversalTime().AddDays(-1);
        var ToDate = createddate.ToUniversalTime.AddDays(1);
        return await guiaToReturn.Where(g => g.StatusHistory.Any(s => s.Fecha > FromDate && s.Fecha < ToDate)).Select(x => x.Id).ToListAsync();
    }
like image 640
pupper9 Avatar asked Sep 04 '25 03:09

pupper9


1 Answers

The Npgsql EF Core provider doesn't currently translate members on DateTimeOffset (see https://github.com/npgsql/efcore.pg/issues/473).

Note that it's somewhat discouraged to use DateTimeOffset with Npgsql, since PostgreSQL doesn't have a type that corresponds to it. DateTimeOffset is mapped to PostgreSQL timestamp with time zone, which doesn't actually have a time zone. If your timestamps are also in UTC, consider using DateTime with Kind=Utc instead.

like image 118
Shay Rojansky Avatar answered Sep 06 '25 00:09

Shay Rojansky