使用GroupBy在物體框架中選擇行-有解無憂

我正在嘗試獲取品牌和型號的所有獨特組合的串列，還包括下表中的最新時間戳和相應顏色：

 ------- --------- ------------------------- -------- 
| 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    |
 ------- --------- ------------------------- --------

在這種情況下，由于制造沃爾沃和型號 XC90 有兩行，我想選擇具有最新時間戳的行并回傳以下內容：

 ------- --------- ------------------------- -------- 
| 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    |
 ------- --------- ------------------------- --------

我嘗試了不同的組合GroupBy和選擇，但沒有成功。這是我正在嘗試做的一個例子，我認為它非常接近，但它拋出了一個例外

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();
            }
        }
     }
}

我收到此錯誤：

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

uj5u.com熱心網友回復：

EF Core 6 應該處理您的查詢。對于 EF Core 5，使用以下模擬，它與 EF Core 6 內部執行的操作相近：

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();
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/444629.html

標籤：C＃林克实体框架核心 ef-core-5.0

上一篇：無法從內部字典/串列中檢索物件

下一篇：如何為燒瓶路由引數指定列舉型別提示