Query when truncate tick in DateTimeOffset or any method to calculated data.

[MinhMit]

I'm developing a small project use postgresql and odata.

and i have an issue when use OrderBy with column has AddTickFunction

MyController:

[HttpGet]
[EnableQuery]
public async Task<IEnumerable<LanguageDto>> GetAsync()
{
    var query = new GetLanguageCollectionQuery();
    return await _mediator.Send(query);
}

I don't want use odata paramters because i need publish both restfull api with /api/languages url and odata /odata/languages url.

Handler

public Task<IQueryable<LanguageDto>> Handle(GetLanguageCollectionQuery request, CancellationToken cancellationToken)
{
    var result = _languageRepository.Queryable().Select(language => new LanguageDto()
    {
        Code = language.Code,
        CreatedBy = language.CreatedBy,
        CreatedTime = language.CreatedTime.Truncate(),
        ModifiedBy = language.ModifiedBy,
        ModifiedTime = language.ModifiedTime.Truncate(),
        DeletedBy = language.DeletedBy,
        DeletedTime = language.DeletedTime.Truncate(),
        IsDeleted = language.IsDeleted,
        Enable = language.Enable,
        Icon = language.Icon,
        Id = language.Id,
        Name = language.Name,
        Translates = language.Translates.Select(translate => new TranslateDto()
        {
            Context = translate.Context,
            Key = translate.Key,
            LanguageId = translate.LanguageId,
            Value = translate.Value
        })
    });
    return Task.FromResult(result);
}

Truncate method:

public static DateTimeOffset Truncate(this DateTimeOffset dateTime, TimeSpan timeSpan)
{
            if (timeSpan == TimeSpan.Zero) return dateTime; // Or could throw an ArgumentException
            if (dateTime == DateTime.MinValue || dateTime == DateTime.MaxValue) return dateTime; // do not modify "guard" values
            return dateTime.AddTicks(-(dateTime.Ticks % timeSpan.Ticks));
 }

Configurate:

private IEdmModel GetEdmModel()
{
    var odataBuilder = new ODataConventionModelBuilder();

    odataBuilder.EntityType<TranslateDto>().HasKey(entity => new { entity.LanguageId, entity.Context, entity.Key });
    odataBuilder.EntitySet<TranslateDto>("Translates");

    odataBuilder.EntityType<LanguageDto>().HasKey(entity => entity.Id);
    odataBuilder.EntitySet<LanguageDto>("Languages");

    var reCreateLanguageAction = odataBuilder.EntityType<LanguageDto>().Action("Recover").ReturnsFromEntitySet<LanguageDto>("Languages");
    var updateTranslateAction = odataBuilder.EntityType<LanguageDto>().Action("UpdateTranslate").Returns<bool>();
    var updateTranslateCollectionAction = odataBuilder.EntityType<LanguageDto>().Action("UpdateTranslates").Returns<bool>();
    var removeTranslateAction = odataBuilder.EntityType<LanguageDto>().Action("RemoveTranslate").Returns<bool>();

    return odataBuilder.GetEdmModel();
}

services.AddControllers().AddOData(opt => { 
    opt.Count().Filter().Expand().Select().OrderBy().SetMaxTop(5000).AddRouteComponents("odata", GetEdmModel());
    opt.TimeZone = TimeZoneInfo.Utc;
}).AddNewtonsoftJson();

It work fine when i use query without truncate datetime method. But i use column has truncate datetime method as CreatedTime, ModifiedBy, DeletedBy. Linq throw new exception The LINQ expression 'DbSet<LanguageReadModel>()\n .Where(l => l.ModifiedTime\n .Truncate() > __TypedProperty_0)' could not be translated.

What can i do to resolve this issue?

Microsoft.AspNetCore.OData (8.0.1)

.NET 5

Thanks for your support.

[Sreejithpin]

Which version of EF are you using. Can you please try using EF6

[MinhMit]

Which version of EF are you using. Can you please try using EF6

Im using:

• Microsoft.EntityFrameworkCore v5.0.9
• Microsoft.EntityFrameworkCore.Relational v5.0.9
• Npgsql.EntityFrameworkCore.PostgreSQL v5.0.7

EF6. your mean is EF core 6.0 Preview?

[KenitoInc]

@MinhMit Try using EF classic 6

[KenitoInc]

@MinhMit Did using EF Classic 6 resolve your issue?

[KenitoInc]

Closing this issue since it's been inactive for long @MinhMit Feel free to reopen incase the issue was not resolved