EF 9 Has a Bug Related to GroupBy Usage: GroupBy Throwing an Exception | HackerNoon

EF9 has a bug and gives an exception “The given key ‘EmptyProjectionMember’ was not present in the dictionary.”

Abstract: Regular usage of EF9 sometimes throws the Exception “The given key ‘EmptyProjectionMember’ was not present in the dictionary.” It seems that is related to the bug in EF9 related to the usage of GroupBy.

I am using Microsoft.EntityFrameworkCore (9.0.2). In my code, I am using GroupBy like this:

 var query2 = query
             .GroupBy(contract =>
                             new { contract.BC_NR, contract.VERTRAGS_NR, contract.VERTRAGS_TYP }
                           )
             .Where(group => group.Any())
             .Select(group =>
                     group.OrderByDescending(contract => contract.CHANGE).First()
                    );

That is working fine when I materialize the query. But, if after that, I do some sorting, it throws an Exception.

var thisWorksFine = iQueryableTBS_VERTRAG2.ToList();
iQueryableTBS_VERTRAG2=iQueryableTBS_VERTRAG2.OrderBy(p=>p.VERTRAGS_NR);
var thisThrowsException = iQueryableTBS_VERTRAG2.ToList();

//Exception: The given key 'EmptyProjectionMember' was not present in the dictionary.

It can be seen in [1] that other people report the same issues. Obviously, that looks like a serious limitation of EF9, or better to say “a bug.”

2. Some Workarounds That Were Tried

There was some unusual workaround suggested on the internet, so I wanted to try it.

var thisWorksFine = iQueryableTBS_VERTRAG2.ToList();
string text22 = iQueryableTBS_VERTRAG2.ToQueryString();
iQueryableTBS_VERTRAG2= ctx.TBS_VERTRAG.FromSqlRaw(text22).AsNoTracking();
var thisStillWorksFine = iQueryableTBS_VERTRAG2.ToList();

iQueryableTBS_VERTRAG2 =iQueryableTBS_VERTRAG2.OrderBy(p=>p.VERTRAGS_NR);

var thisThrowsAnotherException = iQueryableTBS_VERTRAG2.ToList();

//Exception: 'FromSql' or 'SqlQuery' was called with non-composable SQL 
//and with a query composing over it. Consider calling 'AsEnumerable' 
//after the method to perform the composition on the client side.

So, that workaround would not work in my case. The problem is that generated SQL, in my case, contains parameter definitions, and the resulting query is not composable. Semicolons are the problem. Here is what my generated SQL looks like; the content of variable text22:

DECLARE @__bankClearing_0 int = 1;
DECLARE @__bankClearing_1 int = 1;

-- Contracts_ContractsListDT

SELECT [t7].[BC_NR], [t7].[VERTRAGS_NR], [t7].[CHANGE], [t7].[VERTRAGS_TYP], [t7].[AENDERUNG], 
[t7].[ARCHIV_BEZ1], [t7].[ARCHIV_BEZ2], [t7].[ARCHIV_BEZ3], [t7].[AUSZUEGE_BIS_VORTAGE], 
[t7].[BERATER_NR], [t7].[CONTRACT_COLLECTION], [t7].[DOCUMENT_BOX], [t7].[ERFASST_DURCH], 
[t7].[ERFOLGLOS_Z], [t7].[ERSTELLT], [t7].[FREIGABE1], [t7].[FREIGABE2], [t7].[FREIGABE_DATUM_1], 
[t7].[FREIGABE_DATUM_2], [t7].[GESPERRT], [t7].[GESPERRT_BIS], [t7].[GESPERRT_VON], 
[t7].[GLAEUBIGER_ID], [t7].[GLAEUBIGER_ID_LONG], [t7].[KUNDEN_NR], [t7].[LASTMOD], [t7].[LAST_CALL], 

......

3. No Better Practical Known Workaround

Article [1] mentions some other workarounds, but they didn’t look practical to me. I simply coded again my EF query to avoid the usage of GroupBy; even that approach might result in an ugly and slower query.

4. Bug in EF 9 Expected to be Fixed in EF 10

In article [1], a person called Shay Rojansky, a member of the Entity Framework team at Microsoft, promises that the bug will be fixed in EF 10. According to [2], the next planned stable release is EF Core 10.0, or just EF10, scheduled for November 2025.

So, until then, we are forced to do workarounds.

References

[1] https://github.com/dotnet/efcore/issues/31209

[2] https://learn.microsoft.com/en-us/ef/core/what-is-new/