Open GerardSmit opened 1 year ago
Hello. I'll just add that I get the same error doing pretty much the exact same thing. Slight difference is that I'm using SqlServer and LocalDB, targeting .NET 6, and using EF Core 7.0.5 with code-first migrations. I made a small runnable sample project that exhibits the error, too.
.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net6.0</TargetFramework>
<RootNamespace>EF7_group_by_select</RootNamespace>
<ImplicitUsings>enable</ImplicitUsings>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.5">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.5" />
</ItemGroup>
<ItemGroup>
<Folder Include="Migrations\" />
</ItemGroup>
</Project>
Program.cs
using Microsoft.EntityFrameworkCore;
namespace EF7_group_by_select
{
public class Program
{
static async Task Main()
{
var dbContext = new MyContext();
var filter = new[] { "foo", "bar" };
var listOfIds = await dbContext.Items
.Where(x => filter.Contains(x.RefId))
.GroupBy(x => x.RefId)
.Select(grp => grp.OrderByDescending(x => x.Date).First())
.Select(x => x.Id)
.ToListAsync();
Console.WriteLine(string.Join(Environment.NewLine, listOfIds));
}
}
public class MyContext : DbContext
{
public DbSet<Item> Items { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=example-db;Integrated Security=True");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Item>().HasData(
new Item { Id = "a1", Date = new(2023, 6, 12), RefId = "foo" },
new Item { Id = "b2", Date = new(2023, 5, 15), RefId = "bar" },
new Item { Id = "c3", Date = new(2023, 1, 10), RefId = "baz" },
new Item { Id = "d4", Date = new(2023, 2, 25), RefId = "foo" },
new Item { Id = "e5", Date = new(2023, 3, 18), RefId = "bar" });
}
}
public class Item
{
public string Id { get; set; }
public DateTime Date { get; set; }
public string RefId { get; set; }
}
}
Exception
System.Collections.Generic.KeyNotFoundException: The given key 'EmptyProjectionMember' was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ProjectionMemberToIndexConvertingExpressionVisitor.Visit(Expression expression)
at Microsoft.EntityFrameworkCore.Query.EntityShaperExpression.VisitChildren(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryTranslationPostprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at EF7_group_by_select.Program.Main() in C:\Dev\Untracked\EF7-group-by-select\Program.cs:line 13
Not sure if it's relevant but I wanted to mention that this worked in the old EF6. Just noticed this error coming up after migration to EF Core.
yeah i've same issue when i need AsQueryable.
here the code:
var latestProducts = dbContext.Products
.GroupBy(p => p.ProductCode)
.Select(g => g.OrderByDescending(p => p.Version).FirstOrDefault())
.Select(s => new {
ProductCode = s.ProductCode,
ProductName = s.ProductName,
LatestVersion = s.Version
});
If you use
GroupBy
,Select
that gets an row from the group withFirstOrDefault
and then try to reduce the amount of columns returned with anotherSelect
, you get an KeyNotFoundException:Full code
.csproj
Program.cs
Expected output
Notes
Changing the query to
First
without the null-check also throws an exception:Changing the query to start from
Reports
(withoutGroupBy
) and then get the largest one, the query works as expected:If I add an
AsAsyncEnumerable
before the select, the query works as intended:Stack traces
Include provider and version information
EF Core version: 7.0.2 Database provider: bug found in
Microsoft.EntityFrameworkCore.SqlServer
reproduced inMicrosoft.EntityFrameworkCore.Sqlite
Target framework: .NET 7.0 (SDK: 7.0.200,27f0a7fa5a
) Operating system: Windows 11 (22H2) IDE: Rider 2022.3.1