When using GroupBy, getting "the given key 'EmptyProjectionMember' was not present in the dictionary"

[sam-wheat]

Possibly related to:

#20277

#22089

#20929

#30905

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 Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ProjectionMemberToIndexConvertingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   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.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.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at EmptyProjectionMemberTest.Program.Main(String[] args) in C:\Projects\EmptyProjectionMemberTest\EmptyProjectionMemberTest\Program.cs:line 26

Reproduce:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
	<OutputType>Exe</OutputType>
	<TargetFramework>net7.0</TargetFramework>
	<ImplicitUsings>enable</ImplicitUsings>
	<Nullable>enable</Nullable>
  </PropertyGroup>
	<ItemGroup>
		<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.5" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.5" />
	</ItemGroup>
</Project>




using Microsoft.EntityFrameworkCore;

namespace EmptyProjectionMemberTest;

internal class Program
{
	static async Task Main(string[] args)
	{
		DbContextOptionsBuilder builder = new();
		builder.UseSqlServer("Data Source=.;Initial Catalog=EmptyProjectionMemberTest;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=false");
		Db db = new(builder.Options);
		await db.Database.EnsureDeletedAsync();
		await db.Database.EnsureCreatedAsync();

		try
		{
			var query = db.ReleaseDates
				.GroupBy(x => new { x.ReleaseID, x.DateReleased })
				.Select(x => x.First())
				.GroupBy(x => x.ReleaseID);

			string sql = query.ToQueryString(); // fails here

			var stuff = query.ToListAsync();
		}
		catch(Exception ex) 
		{
			Console.WriteLine(ex.ToString()); // The given key 'EmptyProjectionMember' was not present in the dictionary.
		}
	}
}


public class Db : DbContext
{
	public Db(DbContextOptions options) : base(options) { }

	public DbSet<ReleaseDate> ReleaseDates { get; set; }
}

public class ReleaseDate
{
	public int ID { get; set; }
	public string ReleaseID { get; set; }
	public DateTime DateReleased { get; set; }
}

[hahn-kev]

I'm also seeing this, my query is a little different though.

queryable.GroupBy(s => s.EntityId)
.Select(g => g.OrderByDescending(s => s.Commit.DateTime).First())
.Select(s => s.Id)
.ToArray()

the second Select breaks everything, without the second select it works fine. This is a simplification but it has the same issue.

[ZoySoy]

Hello. I also could reproduce "The given key 'EmptyProjectionMember' was not present in the dictionary" exception but on the little bit different example. I faced with this problem during migration from EntityFramework 6.4.4 to EF core. On EF 6.4.4 this query worked well. I have prepared similar repro example based on default blog/post example. There is failed query example:

using var db = new BloggingContext();
var posts = db.Posts
    .GroupBy(
        p => p.BlogId, 
        (id, list) => list.OrderByDescending(x => x.PostId).First())
    .Where(p => p.Title != null)
   .ToList();

For this particular case the problem can be solved by moving Where(...) method before GroupBy(...), but maybe this case will help you to find initial problem anyway because, as I mentioned, it worked previously and I don't think that it is such an unusual query which shouldn't work.

Frameworks:
net6.0
EntityFrameworkCore - 7.0.13

Thanks in advance.

[lucasbode]

I encountered a similar error with a query like this...

var results = Context.TruckModel
            .GroupBy(v => v.TruckModelId)
            .Select(g => g.OrderByDescending(x => x.Version).FirstOrDefault())
            .Where(t => t.Name == truckModelName)
            .ToList();

It looks silly, but we were able to work around it by doing this...

var groupedQuery = Context.TruckModel
            .GroupBy(v => v.TruckModelId)
            .Select(g => g.OrderByDescending(x => x.Version).FirstOrDefault());

var results = Context.TruckModel.FromSqlRaw(groupedQuery.ToQueryString())
            .Where(t => t.Name == truckModelName)
            .ToList();

[lvyyln]

Any updates on this one?

[roji]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 8.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[kwakubiney]

Facing similar issue with this:

     _dbContext.Crashes
                .Where(x => x.ShopId == shopId)
                .OrderByDescending(x => x.CreatedAt)
                .GroupBy(t => t.SerialNumber)
                .Select(g => g.First())
                .AsNoTracking();

EFCore 7.0.0
.NET 6

[Khungersumit]

Okay, so here is the discussion about this issue. I am having the same issue.

query = groupBy(c=>some el)
Select(g => g.OrderByDescending(m => m.CreatedAt).FirstOrDefault())

// Culprit
query= query.OrderByDescending(c => c.CreatedAt);

This additional sorting operation seems to cause problems. Without this final sort, everything works as expected. I've spent hours trying to figure it out and even tried writing a raw SQL query, but that didn’t solve the problem either. I'm stumped! 🤔

[Khungersumit]

Facing similar issue with this:

     _dbContext.Crashes
                .Where(x => x.ShopId == shopId)
                .OrderByDescending(x => x.CreatedAt)
                .GroupBy(t => t.SerialNumber)
                .Select(g => g.First())
                .AsNoTracking();

Resolved using @lucasbode 's approach although it looks clumsy. Tried the other approaches, didn't work:

    var groupedQuery = _dbContext.Crashes
                .GroupBy(v => v.SerialNumber)
                .Select(g => g.OrderByDescending(x => x.CreatedAt).FirstOrDefault());

            var results = _dbContext.Crashes.FromSqlRaw(groupedQuery.ToQueryString())
                .Where(c => c.ShopId!= null && c.ShopId.Equals(shopId));

EFCore 7.0.0 .NET 6

Unfortunately, this workaround didn't pan out for me.

[TimFeldmann-Work]

I am also running into this exact issue and spent the past 5 hours trying to find a workaround with no success.

[Khungersumit]

I am also running into this exact issue and spent the past 5 hours trying to find a workaround with no success.

can you share your query?