Entities:

public class User
{
  public long Id { get; }
  public ICollection<File> Files { get; }
}

public class File
{
  public long Id { get; }
  public DateTime CreatedAt { get; }
  public string Name { get; }
  public User? User { get; }   // nullable for other reasons
}

I want the name of the latest file for each user. I tried:

var data = await context
  Files
  .Where(x => x.User != null)
  .GroupBy(x => x.User!.Id)
  .Select(x => x.MaxBy(y => y.CreatedAt)!)
  .ToDictionaryAsync(k => k.User.Id, v => v.Name);

But that throws

The LINQ expression ... could not be translated

I assume MaxBy is unsupported (in EF Core 8).

Is there a workaround?

Entities:

public class User
{
  public long Id { get; }
  public ICollection<File> Files { get; }
}

public class File
{
  public long Id { get; }
  public DateTime CreatedAt { get; }
  public string Name { get; }
  public User? User { get; }   // nullable for other reasons
}

I want the name of the latest file for each user. I tried:

var data = await context
  Files
  .Where(x => x.User != null)
  .GroupBy(x => x.User!.Id)
  .Select(x => x.MaxBy(y => y.CreatedAt)!)
  .ToDictionaryAsync(k => k.User.Id, v => v.Name);

But that throws

The LINQ expression ... could not be translated

I assume MaxBy is unsupported (in EF Core 8).

Is there a workaround?

• c#
• postgresql
• -core
• entity-framework-core
• ef-core-8.0

• 1 Which DBMS do you target? – Klaus Gütter Commented Feb 10 at 9:09
• 1 .OrderBy(...).Take(1) instead works? – Fildor Commented Feb 10 at 9:10
• @KlausGütter Postgres – lonix Commented Feb 10 at 9:11
• 3 The duplicates both mention OrderByDescending(..).FirstOrDefault() as an alternative. It shouldn't be hard to apply that to your model. Also, there are more questions asking for alternatives of MaxBy. – Gert Arnold Commented Feb 10 at 10:37
• 1 What GH issue do you refer to? A common way to return 1 inner row per outer row is to use SELECT NAME From Files Where Files.UserID=outer.ID ORDER BY Created Desc LIMIT 1. The LINQ equivalent is OrderByDescending(..).FirstOrDefault(). That's what the duplicates show. If you started from Users instead of Files you could write context.Users.ToDictionary(x=>x.Id,x=>x.Files.OrderBy.....) directly. – Panagiotis Kanavos Commented Feb 10 at 12:54

1 Answer 1

Thanks to the comment by @PanagiotisKanavos above, the simplest solution is:

await context
  .Users
  .Include(x => x.Files)
  .Where(x => x.Files.Any())
  .ToDictionaryAsync(
    x => x.Id, 
    x => x.Files.OrderByDescending(y => y.CreatedAt).First().Name)

That completely avoids the MaxBy issue.

Vote here for them to support it.