Open buecherlstefan opened 2 years ago
In SQL, GROUP BY requires using an aggregate operator - Count, Max - to reduce the grouped rows. See the docs for more information.
You can write like this
var query = _repository.Query
.GroupBy(x => x.ApiName)
.Select(g => g.Take(count))
.SelectMany(x => x)
;
You can write like this
var query = _repository.Query .GroupBy(x => x.ApiName) .Select(g => g.Take(count)) .SelectMany(x => x) ;
This fails with this btw: "error": "System.InvalidOperationException: The LINQ expression 'x => x' 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'.
I am having exactly the same issues, I am finding the Groupby does not work at all
I stumbled up on the same issue. GroupBy is not working with SelectMany.
I think I might be having the same issue, did anyone find a workaround yet?
I think I might be having the same issue, did anyone find a workaround yet?
I did not.
Workaround for me is to retrieve the data and then SelectMany(), so the expression don't have to be translated to sql
public IEnumerable<ProductInfoRawData> ListLastInsertedOnesPerApi(int count = 5)
{
//
// LISTING 1
// var query = _repository.Query.GroupBy(x => x.ApiName).SelectMany(x => x.Take(count));
//
// LISTING 1 throws The LINQ expression could not be translated
// FIX: Do it with IEnumerable
var enumerable = _repository.Query
.GroupBy(x => x.ApiName)
.Select(x => x.OrderByDescending(y => y.DateUtc).Take(count))
.ToList()
.SelectMany(x => x);
return enumerable;
}
This is my solution. I replace the code just for demo purposes.
_context.Tags
.Where(x => x.CreatedBy == request.CreatedBy)
.GroupBy(x => x.MeasuredAt)
.OrderByDescending(y => y.Key)
.Take(5)
.Select(x => x.Select(x => new NewModel {
Id = x.Id,
CreatedAt = x.CreatedAt,
CreatedBy = x.CreatedBy,
MeasuredAt = x.MeasuredAt,
Value = x.Value
}))
.ToList()
.SelectMany(x => x);
I'm surprised that @donguyenthanhlan solution works, and specially when the following does not
_context.Tags
.Where(x => x.CreatedBy == request.CreatedBy)
.GroupBy(x => x.MeasuredAt)
.OrderByDescending(y => y.Key)
.Take(5)
.SelectMany(x => x.Select(x => new NewModel {
Id = x.Id,
CreatedAt = x.CreatedAt,
CreatedBy = x.CreatedBy,
MeasuredAt = x.MeasuredAt,
Value = x.Value
}))
.ToList();
Is there any plan/way to support such requests ? They seem pretty regular to me.
@Poltuu yeah, we'll likely do a big push on GroupBy functionality - there's a broad set of issues in the implementation that need to be fixed. That's unfortunately unlikely to happen for 9.0 - but I'm hoping we'll do it for 10 at the latest.
Include your code
Include stack traces
Include provider and version information
EF Core version: 6.0.5 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.1.6