Search code examples
c#entity-framework-coremaxby

LINQ to Entities and MaxBy

I want to try to use MaxBy for type DateTime

My model is:

public class DownloadCenterFolder
{
    public int Id { get; set; }
    public required string FolderName { get; set; }

    public virtual ICollection<DownloadCenterFile> DownloadCenterFiles { get; protected set; } = new List<DownloadCenterFile>();
}

public class DownloadCenterFile
{
    public int Id { get; set; }
    public required string FileNameFriendly { get; set; }
    public required string FileNameUnique { get; set; }
    public DateTime DateAdded { get; set; }
    public DateTime? DateLastUpdated { get; set; }
    public long ContentLenght { get; set; }

    public int FolderId { get; set; }
    public virtual DownloadCenterFolder DownloadCenterFolder { get; set; }
}

and DTO class:

public class DownloadCenterFolderElementDto
{
    public int FolderID { get; set; }
    public string FolderName { get; set; }
    public DateTime? DateLastUpdated { get; set; }
    public long Size { get; set; }
}

and automapper rule:

    CreateMap<DownloadCenterFolder, DownloadCenterFolderElementDto>()
        .ForMember(d => d.FolderID, o => o.MapFrom(s => s.Id))
        .ForMember(d => d.Size, o => o.MapFrom(i => (i.DownloadCenterFiles.Count() > 0) ? i.DownloadCenterFiles.Sum(p => p.ContentLenght) : 0))
        .ForMember(d => d.DateLastUpdated, o => o.MapFrom(i => (i.DownloadCenterFiles.Count() > 0) ? i.DownloadCenterFiles.MaxBy(p => p.DateAdded).DateAdded : (DateTime?)null))
        ;

and call:

    var list = await _db.DownloadCenterFolders.ProjectTo<DownloadCenterFolderElementDto>(_mapperConfig).ToListAsync();

Unit Tests says ok, but real call to SQL throws an exception:

InvalidOperationException: The LINQ expression 'MaterializeCollectionNavigation( Navigation: DownloadCenterFolder.DownloadCenterFiles, subquery: DbSet() .Where(d1 => EF.Property<int?>(d, "Id") != null && object.Equals( objA: (object)EF.Property<int?>(d, "Id"), objB: (object)EF.Property<int?>(d1, "FolderId"))) .Where(i => EF.Property<int?>(d, "Id") != null && object.Equals( objA: (object)EF.Property<int?>(d, "Id"), objB: (object)EF.Property<int?>(i, "FolderId")))) .AsQueryable() .MaxBy(p => p.DateAdded)' 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.com/fwlink/?linkid=2101038 for more information.

if I change MaxBy query to :

.ForMember(d => d.DateLastUpdated, o => o.MapFrom(i => (i.DownloadCenterFiles.Count() > 0) ? i.DownloadCenterFiles.OrderByDescending(p => p.DateAdded).Select(p => p.DateAdded).FirstOrDefault() : (DateTime?)null))

it works fine. I thought, MaxBy is equivalent of OrderByDescending(..).FirstOrDefault() but now is a little confused. How so and how to use it correctly?

Solution

  • Unfortunately, the fact that some LINQ query works fine in LINQ-to-Objects, doesn't mean it will also work fine in EFCore. Additionally, the fact that one operator might be "equivalent" to a sequence of other operators that are known to work, also doesn't mean the "composite" operator will work, as they are analyzed in isolation to one another.

    The way EFCore works is by taking the IQueryable's abstract syntax tree and trying to see which pieces match which SQL statements. It sees a "MaxBy" call in the tree and currently doesn't know how to convert that to SQL. Not all LINQ operators are supported, and some will throw the exception you saw there.

    Support for MinBy/MaxBy hasn't been implemented yet as you can see from this issue: