linq - Date could not be translated in filter using Entity Framework - Stack Overflow

admin管理员组

文章数量:1406950

System.InvalidOperationException: 'The LINQ expression 'DbSet() .Where(a => a.Id == __Id_0) .Where(a => a.DateTimeStarted >= __p_1)' 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 /?linkid=2101038 for more information.'

public async Task<IEnumerable<Action>> GetFilteredActions(Guid Id, DateTimeOffset? startDate, DateTimeOffset? endDate)
{
    IQueryable<Activity> query = _context.Activities.Where(a => a.Id == Id);

    // Filter by start date if provided
    if (startDate.HasValue)
    {
        query = query.Where(a => a.DateTimeStarted >= startDate.Value);
    }


    // Filter by end date if provided
    if (endDate.HasValue)
    {
        query = query.Where(a => a.DateTimeEnd <= endDate.Value);
    }

    return await query.ToListAsync();
}

System.InvalidOperationException: 'The LINQ expression 'DbSet() .Where(a => a.Id == __Id_0) .Where(a => a.DateTimeStarted >= __p_1)' 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/fwlink/?linkid=2101038 for more information.'

public async Task<IEnumerable<Action>> GetFilteredActions(Guid Id, DateTimeOffset? startDate, DateTimeOffset? endDate)
{
    IQueryable<Activity> query = _context.Activities.Where(a => a.Id == Id);

    // Filter by start date if provided
    if (startDate.HasValue)
    {
        query = query.Where(a => a.DateTimeStarted >= startDate.Value);
    }


    // Filter by end date if provided
    if (endDate.HasValue)
    {
        query = query.Where(a => a.DateTimeEnd <= endDate.Value);
    }

    return await query.ToListAsync();
}

• entity-framework
• linq

Share Improve this question Follow edited Mar 4 at 20:32 Sonam Mohite asked Mar 4 at 10:13 Sonam MohiteSonam Mohite 90344 gold badges1919 silver badges5757 bronze badges 2

• 1 I would vote to close this question. The provided code should work in 99% of cases, with the remaining 1% potentially affected by factors such as mapping configurations, model classes, EF version, and the database provider used. To keep the question open, please provide additional details on these aspects. – Svyatoslav Danyliv Commented Mar 4 at 11:09
• I don't believe DateTimeOffset is supported by EF Core. If you are storing data across different timezones then consider storing UTC dateTime values and being sure that any client-side parameter values are converted to their respective UTC time. Additionally the endDate logic should likely be < endDate or <= endDate depending on how you want to cut off the value. – Steve Py Commented Mar 4 at 11:40

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

 protected override void OnModelCreating(ModelBuilder builder)
 {
     base.OnModelCreating(builder);

     if (Database.IsSqlite())
     {
         // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
         // here: https://docs.microsoft/en-us/ef/core/providers/sqlite/limitations#query-limitations
         // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
         // use the DateTimeOffsetToBinaryConverter
         // Based on: https://github/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
         // This only supports millisecond precision, but should be sufficient for most use cases.
         foreach (var entityType in builder.Model.GetEntityTypes())
         {
             var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)
                                                                         || p.PropertyType == typeof(DateTimeOffset?));
             foreach (var property in properties)
             {
                 builder
                     .Entity(entityType.Name)
                     .Property(property.Name)
                     .HasConversion(new DateTimeOffsetToBinaryConverter());
             }
         }
     }
 }

Achieved it by above code

本文标签： linqDate could not be translated in filter using Entity FrameworkStack Overflow