Entity Framework Core - Sorting on Calculated Field

user_12156320 0
Viewed 1483

I have a model similar to this:

Public class Item {
  public int Id { get; set; }
  public string Name { get; set; }
  public DateTime? StartTime { get; set; }
  public DateTime? EndTime { get; set; }
  public int? TotalTime
  {
    get
    {
      return Convert.ToInt32(EndTime.Value - StartTime.Value).TotalMinutes);
    }
  }
  etc...

Then I get a list of Items:

IQueryable<Items> items = itemsContext.Items.Where(e => e.Id > 0);

I do some calculations, run some other code, then at the end I want to return the list of Items ordered by Name ASC and TotalTime DESC. This is what I tried:

items = items.OrderBy(e => e.Name).ThenByDescending(e => e.TotalTime);

But I get an error: "The LINQ expression 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()."

I also tried this, but get the same error:

items = items.OrderBy(e => e.Name).ThenByDescending(e => e.TotalTime ?? 0);

How can I sort my data set by TotalTime?

asp.net-core .net-core entity-framework linq c#
2 Answers
  • As TotalTime is not part of your database, so you need to annotate it with [NotMapped].
  • Then you need to first load the items and then sort by them.
  • However if you want to perform paging or sorting within your database server then you need to use EndTime and StartTime directly in your query.

Approach 1: Loading first, then sorting in application code:

List<Items> loadedItems = await itemsContext.Items
    .Where( e => e.Id > 123 )
    .ToListAsync()
    .ConfigureAwait(false); // `ConfigureAwait(false)` MAY be optional, depending on your application.

List<Items> sortedItems = loadedItems
    .OrderBy(e => e.Name)
    .ThenByDescending(e => e.TotalTime)
    .ToList(); // <-- Note this is NOT `ToListAsync` because the items are already loaded into memory.

Approach 2: Sorting in SQL:

List<Items> loadedSortedItems = await itemsContext.Items
    .Where( e => e.Id > 123 )
    .OrderBy( e => e.Name )
    .ThenByDescending( e => e.EndTime - e.StartTime )
    .ToListAsync()
    .ConfigureAwait(false);

Changes to your class

I'd modify your class to be something like this:

public class Item {
  public int       Id        { get; set; }
  public string    Name      { get; set; }
  public DateTime? StartTime { get; set; }
  public DateTime? EndTime   { get; set; }
  
  [NotMapped]
  public int? TotalTimeMinutes
  {
    get
    {
        if( this.EndTime == null || this.StartTime == null ) return null;

        TimeSpan diff = this.EndTime.Value - this.StartTime.Value;
        return (Int32)Math.Round( diff.TotalMinutes );
    }
  }
  • The NotMapped attribute makes it clear to Entity Framework that it should ignore the property.
  • Note how the property includes the unit name ("Minutes") otherwise it's non-obvious if the property is milliseconds, seconds, minutes, etc.
  • Also note how the property checks if StartTime and EndTime are null and returns null in that case. Your code did not, which means your program will crash if ther are NULL values in the database or if any Item object instances are not fully initialized.
  • If StartTime and EndTime values cannot be NULL then you need to add NOT NULL to your database and update your EF entity classes so the properties are DateTime and not DateTime?.
user_1591450

You should translate the SQL DateDiff method to EF Core. for this approach, you can use from below method.

EF.Functions.DateDiffMinute(c.StartDate, c.EndDate ?? DateTime.Now)
user_109107800
Related