I'm trying to get a list of all unique combinations of make and model and also include the most recent time stamp and the corresponding color from the following table:
+-------+---------+-------------------------+--------+
| Make | Model | Last Seen | Color |
+-------+---------+-------------------------+--------+
| Volvo | XC90 | 2022-03-11 13:30:31.623 | Blue |
| Volvo | XC90 | 2022-03-11 14:30:31.623 | Green |
| BMW | M3 | 2022-03-11 15:30:31.623 | Orange |
| Ford | Mustang | 2022-03-11 16:30:31.623 | Red |
+-------+---------+-------------------------+--------+
In this case since there are two rows with the make Volvo and Model XC90, I want to pick the row with the latest timestamp and return the following:
+-------+---------+-------------------------+--------+
| Make | Model | Last Seen | Color |
+-------+---------+-------------------------+--------+
| Volvo | XC90 | 2022-03-11 14:30:31.623 | Green |
| BMW | M3 | 2022-03-11 15:30:31.623 | Orange |
| Ford | Mustang | 2022-03-11 16:30:31.623 | Red |
+-------+---------+-------------------------+--------+
I have tried different combinations of GroupBy and select but without success. Here is an example of what I'm trying to do which I think is very close, but it's throwing an exception
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace TestEntityFramework
{
class Program
{
public class Car
{
public Guid Id { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public DateTime Time { get; set; }
public string Color { get; set; }
}
public class CarContext : DbContext
{
public CarContext(DbContextOptions<CarContext> options) : base(options)
{
}
public DbSet<Car> Cars { get; set; }
}
static async Task Main(string[] args)
{
DbContextOptions<CarContext> _options = new DbContextOptionsBuilder<CarContext>()
.UseInMemoryDatabase("CarsDataBase")
.Options;
Car car1 = new Car() { Id = Guid.NewGuid(), Make = "Volvo", Model = "VC90", Time = DateTime.Now, Color = "Blue" };
Car car2 = new Car() { Id = Guid.NewGuid(), Make = "Volvo", Model = "VC90", Time = DateTime.Now, Color = "Green" };
Car car3 = new Car() { Id = Guid.NewGuid(), Make = "BMW", Model = "M3", Time = DateTime.Now, Color = "Red" };
Car car4 = new Car() { Id = Guid.NewGuid(), Make = "Ford", Model = "Mustang", Time = DateTime.Now, Color = "Orange" };
using (var context = new CarContext(_options))
{
await context.Cars.AddAsync(car1);
await context.Cars.AddAsync(car2);
await context.Cars.AddAsync(car3);
await context.Cars.AddAsync(car4);
await context.SaveChangesAsync();
}
using (var context = new CarContext(_options))
{
var cars = context.Cars.GroupBy(o => new { o.Make, o.Model })
.Select(n => n.OrderByDescending(c => c.Time).First())
.ToList();
}
}
}
}
I get this error:
System.InvalidOperationException HResult=0x80131509 Message=The LINQ expression 'GroupByShaperExpression: KeySelector: new {
Make = ExpressionExtensions.ValueBufferTryReadValue<string>(
valueBuffer: grouping.Key,
index: 0,
property: Property: Car.Make (string)),
Model = ExpressionExtensions.ValueBufferTryReadValue<string>(
valueBuffer: grouping.Key,
index: 1,
property: Property: Car.Model (string)) }, ElementSelector:EntityShaperExpression:
EntityType: Car
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.OrderByDescending(c => c.Time)' 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. Source=Microsoft.EntityFrameworkCore.InMemory StackTrace: at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.TranslateInternal(Expression expression) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Translate(InMemoryQueryExpression queryExpression, Expression expression) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at TestEntityFramework.Program.<Main>d__2.MoveNext() in C:\Apps\TestEntityFramework\Program.cs:line 52
EF Core 6 should handle your query. For EF Core 5, use the following emulation which is close what EF Core 6 do internally:
using (var context = new CarContext(_options))
{
var query = context.Cars;
var carQuery =
from d in query.Select(o => new { o.Make, o.Model }).Distinct()
from car in query
.Where(car => car.Make == d.Make && car.Model == d.Model)
.OrderByDescending(car => car.Time)
.Take(1)
select car;
var cars = carQuery.ToList();
}