Npgsql in EFCore cannot translate DateTimeOffset.Date

Question

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();
    }

Shay Rojansky Shay Rojansky · Accepted Answer · 2021-03-31T04:53:47

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.

Npgsql in EFCore cannot translate DateTimeOffset.Date

1 Answers