Translate GroupBy followed by FirstOrDefault over group

[smitpatel]

No description provided.

[RainingNight]

??

[hannan-sultan]

Is there any update? Any alternative or planned fix? @ajcvickers @shanselman

[collinbarrett]

Is there a workaround for this that would allow for database-evaluated DistinctBy() logic? Seems like this Issue blocks database evaluation of this.

[madhav5589]

Any workaround while it's being fixed? I am in the process of migrating .NET Core from 2.1 to 3.1 and EF Core from 2.1 to 3.1.

[suadev]

Another breaking change for migrating to ef core 3.1 and nobody knows when it will be supported :( @smitpatel @ajcvickers

[smitpatel]

@suadev - It was getting client eval'ed before. It is part of same breaking change disabling client eval. The perf was bad for this case. If your group had 100 element each and you are selecting only first, we still got data for all 100 elements to client side to compute the first.

[reathh]

Very important feature IMHO.
My use case is this:

await _db.RetailerRates
                .GroupBy(r => r.Price)
                .Select(g => g.OrderByDescending(r => r.CreatedAt).First())
                .ToListAsync();

[GSPP]

This query pattern shows up quite a bit in my under-way 1000 table LINQ to SQL migration project.

Example use case: You have a list of blogs. For each blog you want to display a single highlighted post (the most upvoted or most recent one). So you might group by blog and take the first item from each group.

This can be translated with ROW_NUMBER ... WHERE r = 1 or with CROSS APPLY (SELECT TOP 1 ...). Both variants have different query plan shapes and performance characteristics. It would be nice if the shape could be selected by writing the LINQ query in a particular way.

This query could result in the ROW_NUMBER shape:

ctx.BlogPosts
.GroupBy(bp => bp.Blog)
.Select(g => g.OrderByDescending(bp => bp.CreatedAt).FirstOrDefault())
.ToList();

This query could result in the CROSS APPLY shape:

ctx.BlogPosts
.GroupBy(bp => bp.Blog)
.SelectMany(g => g.OrderByDescending(bp => bp.CreatedAt).Take(1).DefaultIfEmpty())
.ToList();

But any way to support this would be better than no way so that our migration can proceed.

---

The ROW_NUMBER plan leads SQL Server to sort all rows which could be billions. The CROSS APPLY lends itself to a loop join plan that very selectively queries an index. ROW_NUMBER is great if the fraction of rows selected is near 100%. CROSS APPLY is great when picking very few items.

Depending on the scenario the performance difference can be significant. SQL Server is unable to automatically switch between theses forms.

[lenniebriscoe]

@smitpatel @ajcvickers Is there any update on this issue or work around you could suggest for my case #21619 please?

If I distil my use-case it comes down to a simple multi-column group-by statement and a projection:

            var groupedOrganisationMentions = await collection
                .GroupBy(m => new {m.LocationId, m.PublicationId, m.PracticeAreaId})
                .Select(am => new
                {
                    Key = am.Key,
                    Items = am.ToList()
                }).ToListAsync(cancellationToken: token).ConfigureAwait(false);

The Items projection just doesn't want to work.

[brandonsmith86]

I've got a similar use case that seems to be related to this issue. I use code like this in several places:

// This works fine
query.Select(price => new Customer {
   Name = price.Payer.Name,
   Code = price.Payer.Code,
   City = price.Payer.City,
   ParentCode = price.Payer.ParentCode,
   ParentLevel = CustomerLevel.Corporate,
   CustomerLevel = CustomerLevel.Payer
}).Distinct().ToListAsync();

That works fine. The query variable could potentially have a variety of expressions and joins with no issue. As soon as I add a call to OrderBy, it will not evaluate. I've tried several workarounds that I've found around the interwebz, and nothing seems to resolve it.

// This throws error
// query is of type IQueryable<Price>
query.Select(price => new Customer {
   Name = price.Payer.Name,
   Code = price.Payer.Code,
   City = price.Payer.City,
   ParentCode = price.Payer.ParentCode,
   ParentLevel = CustomerLevel.Corporate,
   CustomerLevel = CustomerLevel.Payer
}).Distinct().OrderBy(cust => cust.Name).ToListAsync();

Also, placement of the OrderBy does not seem to matter.

// This also throws error
// query is of type IQueryable<Price>
query
   .OrderBy(price => price.payer.Name)
   .Select(price => new Customer {
      Name = price.Payer.Name,
      Code = price.Payer.Code,
      City = price.Payer.City,
      ParentCode = price.Payer.ParentCode,
      ParentLevel = CustomerLevel.Corporate,
      CustomerLevel = CustomerLevel.Payer
   }).Distinct().ToListAsync();

[NetMage]

@smitpatel

@suadev - It was getting client eval'ed before. It is part of same breaking change disabling client eval. The perf was bad for this case. If your group had 100 element each and you are selecting only first, we still got data for all 100 elements to client side to compute the first.

I don't think you can assume that - I believe this worked fine in EF 6 / LINQ to SQL so I wouldn't assume was using EF Core 2.x (I would never assume that - it was barely functional.)

@ajcvickers

@stevozilik EF6 runs on .NET Core, so even if you can't use EF Core due to this, it should not stop you migrating to .NET Core.

Will EF 6 be supported on .Net 6?

[smitpatel]

@NetMage - The comment from suadev indicated that they ran into another breaking change when upgrading to EF Core 3.1. So yes it is fair to assume it is coming from earlier version of EF Core. When upgrading from EF6/LINQ to SQL, the concept of running into breaking change has no meaning. Changing from one product to another product will require code changes and there are no "breaking changes" in such change.

[Ruud-cb]

Wondering what all of you have been using as a workaround up until this is fixed? Last time i got away with it by caching all the results and doing to query in the app instead of linq-to-sql. This time I really have to do it like @reathh shows.

So if anyone got a workaround, that would be great.

Very important feature IMHO.
My use case is this:

await _db.RetailerRates
                .GroupBy(r => r.Price)
                .Select(g => g.OrderByDescending(r => r.CreatedAt).First())
                .ToListAsync();

[DK8ALREC]

I got away with something like this before:

await _db.RetailerRates
  .GroupBy(x => x.Price)
  .Select(x => new { CreatedAt = x.Max(y => y.CreatedAt), Price = x.Key})
  .Join(_db.RetailerRates, x => x, x => new { x.CreatedAt, x.Price }, (_, retailerRate) => retailerRate);
  .ToListAsync();

Disclaimer: This solution depends on the uniqueness of your Price/CreatedAt combination. If not unique, then you might end up with multiple entities for each Price/CreatedAt combination. You could do some extra filtering after materializing the query to accomodate for this 🙂

[LeaFrock]

@Ruud-cb Try to use the codes below,

await _db.RetailerRates
                .Select(r => r.Price)
                .Distinct()
                .SelectMany(p => _db.RetailerRates.Where(r => r.Price == p).OrderByDescending(r => r.CreatedAt).Take(1))
                .ToListAsync()

For SQL Server, it will generate the sql with partition & inner join. The result is the same as group by.

[DK8ALREC]

@Ruud-cb Try to use the codes below,

await _db.RetailerRates
                .Select(r => r.Price)
                .Distinct()
                .SelectMany(p => _db.RetailerRates.Where(r => r.Price == p).OrderByDescending(r => r.CreatedAt).Take(1))
                .ToListAsync()

For SQL Server, it will generate the sql with partition & inner join. The result is the same as group by.

I think .Distinct() will materialize all distinct prices?

[LeaFrock]

@DK8ALREC Yes, it works as same as group by. And for linq-to-sql, it will be generated as select distinct(r.Price) from [RetailerRates] as [r].

[DK8ALREC]

@DK8ALREC Yes, it works as same as group by. And for linq-to-sql, it will be generated as select distinct(r.Price) from [RetailerRates] as [r].

Yes, my point was that I think your approach would make 2 database queries, and it will materialize all distinct Prices before making the "final query", which could potentially be a lot of rows I guess.

[LeaFrock]

Yes, my point was that I think your approach would make 2 database queries, and it will materialize all distinct Prices before making the "final query", which could potentially be a lot of rows I guess.

No, it won't. You just need to take a try :)