The given key 'EmptyProjectionMember' was not present in the dictionary

[sam-wheat]

Possibly related to:

https://github.com/dotnet/efcore/issues/20277

https://github.com/dotnet/efcore/issues/22089

https://github.com/dotnet/efcore/issues/20929

https://github.com/dotnet/efcore/issues/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();

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

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

[TimFeldmann-Work]

Using your sample:

var results = await _dbContext.Crashes
    .GroupBy(
      v => new {v.Name, v.Color, v.Make },
      // Retrieve only the latest crash per Name / Color / Make group.
      (key, group) => group.OrderByDescending(v => v.Year).First())
    // This is where it breaks.
    // I want to order the final result set by year.
    // If I remove this OrderByDescending, the query works fine.
    .OrderByDescending(g => g.Year)
    .ToListAsync());

I continued to troubleshoot this after I made my last post desperate for any kind of a solution.

It's been a total of 10 hours today and I settled on the worst code I've written in 3 years:

I'm using IQueryable.TagWith("X") to add a query hint to the final SQL. Then, I registered an interceptor to check for the tag and manually append a raw SQL order by clause depending on which tag I added.

This is in my opinion a very dirty solution to this problem, but I wasn't able to find an alternative solution. The proposed .ToQueryString() workaround does not work for me because my GroupBy clause contains parameters and I was not able to locate a way to return both the SQL string as well as the parameters.

I had the horrible thought of parsing out the SQL string returned by ToQueryString() with its parameters at the top and manually reconstructing them in code, but that was one step too far into degeneracy for me.

I understand fully that translating all forms of LINQ to SQL is an astronomical task. However, it would be great if there was a way to manually append any arbitrary SQL to the end of whatever SQL Entity Framework generates so I don't have to rely on application-wide interceptors which run for all SQL commands.

A simple IQueryable.AppendSql(sql, parameters) method would be amazing for these types of scenarios. This workaround would still not have been preferred, however it would have been better than my interceptor.

Considering this issue has been open for over a year, I'm assuming it's not a high priority, however my job relies on this functionality as the base IQueryable is dynamically generated from a bunch of parameters and it works great, except for this part.

You could make the argument that I could just use raw SQL for this, however the SQL my application works with is highly dynamic depending on a wide and dynamic set of parameters and I wasn't looking to build out a flaky type-unsafe SQL builder for this.

I've been working with EF Core for close to 6 years almost non-stop and as my basis for building internal developer frameworks and I have to say, it's my favorite framework period. This is the first time I've run into a problem requiring such a major hack like this.

Edit: I'll also add, performing client-side ordering would not have worked as I'm expecting these queries to return 100s of millions / potentially billinos of records streamed via an IAsyncEnumerable.

[Khungersumit]

Using your sample:

var results = await _dbContext.Crashes
    .GroupBy(
      v => new {v.Name, v.Color, v.Make },
      // Retrieve only the latest crash per Name / Color / Make group.
      (key, group) => group.OrderByDescending(v => v.Year).First())
    // This is where it breaks.
    // I want to order the final result set by year.
    // If I remove this OrderByDescending, the query works fine.
    .OrderByDescending(g => g.OrderByDescending(x => x.Year).ToListAsync());

I continued to troubleshoot this after I made my last post desperate for any kind of a solution.

It's been a total of 10 hours today and I settled on the worst code I've written in 3 years:

I'm using IQueryable.TagWith("X") to add a query hint to the final SQL. Then, I registered an interceptor to check for the tag and manually append a raw SQL order by clause depending on which tag I added.

This is in my opinion a very dirty solution to this problem, but I wasn't able to find an alternative solution. The proposed .ToQueryString() workaround does not work for me because my GroupBy clause contains parameters and I was not able to locate a way to return both the SQL string as well as the parameters.

I had the horrible thought of parsing out the SQL string returned by ToQueryString() with its parameters at the top and manually reconstructing them in code, but that was one step too far into degeneracy for me.

I understand fully that translating all forms of LINQ to SQL is an astronomical task. However, it would be great if there was a way to manually append any arbitrary SQL to the end of whatever SQL Entity Framework generates so I don't have to rely on application-wide interceptors which run for all SQL commands.

A simple IQueryable.AppendSql(sql, parameters) method would be amazing for these types of scenarios. This workaround would still not have been preferred, however it would have been better than my interceptor.

Considering this issue has been open for over a year, I'm assuming it's not a high priority, however my job relies on this functionality as the base IQueryable is dynamically generated from a bunch of parameters and it works great, except for this part.

You could make the argument that I could just use raw SQL for this, however the SQL my application works with is highly dynamic depending on a wide and dynamic set of parameters and I wasn't looking to build out a flaky type-unsafe SQL builder for this.

I've been working with EF Core for close to 6 years almost non-stop as my basis for building internal developer frameworks. This is the first time I've run into a problem requiring such a major hack like this.

Edit: I'll also add, performing client-side ordering would not have worked as I'm expecting these queries to return 100s of millions / potentially billinos of records streamed via an IAsyncEnumerable.

I understand your frustration as I had the same case, I tried writing a raw SQL query but that turned out to be hell and didn't pan out either, we're helpless here unless someone from EF team look into this issue 🫥

[TimFeldmann-Work]

Yeah, I completely sympathize with the EF team, however a "catch all" solution for manually appending SQL to the end of an IQueryable would be great as a short / medium-term hacks until more translations are supported for niche uses.

Voiding that, being able to scope interceptors to specific DbSets would be great as well.

For now, I'll tuck my interceptor away into some dark corner of my project with a hand-written apology note to whoever comes across it lol.

[roji]

Everyone, this issue is on my radar; we have quite a few issues with the current GroupBy implementation (see the area-groupby label), and I hope to make a concentrated push on the whole area for EF 10. GroupBy translation is unfortunately a particularly tricky area in LINQ to SQL translation.

I fully understand the frustration here, and EF should indeed be translating these queries correctly (or at least throwing a clear exception where a translation really isn't possible).

In the meantime, EF does provide escape-hatch workarounds - interceptors can be used to add SQL, and SQL queries can allow you to specify the exact SQL you want, and then compose additional LINQ operators on top and use EF to materialize the results. These are imperfect workarounds until we solve the real problems here.

[TimFeldmann-Work]

Thank you @roji, it's very much appreciated!

Regarding the workarounds, I spent a few hours trying to use SQL + LINQ composition, however I was looking for a way to start with LINQ and then compose SQL over the LINQ expressions, instead of starting with SQL and then composing LINQ on top of the SQL which I don't think is possible unless I'm mistaken.

On a side / off-topic note, I've been building a Rebus competitor for the past 3 years using EF + jsonb and dynamic DBSets as the basis for managing Sagas and it's been an absolute pleasure. I've been reading your comments along with Aj's on various GitHub issues on a weekly basis for several years and you guys have been absolutely incredible, I'm always astonished by what you guys do.

I know it's corny, but thank you for building this incredible technology including the Npgsql implementation, it's an engineering marvel in my opinion.

[roji]

I was looking for a way to start with LINQ and then compose SQL over the LINQ expressions, instead of starting with SQL and then composing LINQ on top of the SQL which I don't think is possible unless I'm mistaken.

That's indeed not possible, there are various complications around the integration of the LINQ expression inside user-provided SQL that have made this unfeasible, at least so far.

And thanks for all the kind words, they are much appreciated!