Could someone explain why one fails but the other does not

[fradsham]

Ask a question

I have two pieces of code. I can get one to run (see the last part of the message) but the other fails to translate. Is this a bug?
Why it is the value "1" being treated as "(Nullable<int>)1"? Is it because "CodeId" is Nullable? Note: IndividualObservationGroup is a join table between ObservationGroup and IndividualObservation

Include your code

Usually the best way to ask a clear question and get a quick response is to show your code. Preferably, attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing.

var queryable = context.IndividualObservationGroup.AsNoTracking()
                                                .Include(obg => obg.ObservationGroup)
                                                .Include(obg => obg.IndividualObservation)
                                                    .ThenInclude(io => io.SetDetail)
                                                        .ThenInclude(sd => sd.Mission)
                                                .Where(items => String.Equals(items.IndividualObservation.SetDetail.Mission.ParentGuid, surveyGuid) &&
                                                                String.Equals(items.ObservationGroupGuid, lookupItem.ObservationGroupGuid) &&
                                                                items.GroupNumber == lookupItem.GroupNumber &&
                                                                items.TargetItem == lookupItem.TargetItem)
                                                .GroupBy(items => items.ObservationGroupGuid)
                                                .Select(items => new
                                                {
                                                    TallyKey = items.Key,
                                                    Females = items.Count(x => x.IndividualObservation.CodeId == 1),
                                                    Males = items.Count(x => x.IndividualObservation.CodeId == 2),
                                                    UnSexed = items.Count(x => x.IndividualObservation.CodeId == 3),
                                                    numberFromCurrentSet = items.Count(x => x.IndividualObservation.ParentGuid == setDetailGuid)
                                                });


                        var group = await queryable.FirstOrDefaultAsync();

Include stack traces

 Message: 
    System.InvalidOperationException : The LINQ expression 'GroupByShaperExpression:
    KeySelector: i.ObservationGroupGuid, 
    ElementSelector:EntityShaperExpression: 
        EntityType: IndividualObservationGroup
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    
        .Count(x => x.IndividualObservation.CodeId == (Nullable<int>)1)' 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.
  Stack Trace: 
    RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
    RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
    RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
    RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
    NewExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
    RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
    RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
    Database.CompileQuery[TResult](Expression query, Boolean async)
    QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
    <>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
    CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
    QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
    EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
    EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
    EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
    EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Include provider and version information

EF Core version: 5.0.2
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .net5.0
Operating system: Windows 10 Pro 10.0.19042
IDE: Visual Studio Enterprise 2019 16.8.5

###NOTE: This will run but logically incorrect, so the lambdas in the item.Count() is the key issue

var queryable = context.IndividualObservationGroup.AsNoTracking()
                                                .Include(obg => obg.ObservationGroup)
                                                .Include(obg => obg.IndividualObservation)
                                                    .ThenInclude(io => io.SetDetail)
                                                        .ThenInclude(sd => sd.Mission)
                                                .Where(items => String.Equals(items.IndividualObservation.SetDetail.Mission.ParentGuid, surveyGuid) &&
                                                                String.Equals(items.ObservationGroupGuid, lookupItem.ObservationGroupGuid) &&
                                                                items.GroupNumber == lookupItem.GroupNumber &&
                                                                items.TargetItem == lookupItem.TargetItem)
                                                .GroupBy(items => items.ObservationGroupGuid)
                                                .Select(items => new
                                                {
                                                    TallyKey = items.Key,
                                                    Females = items.Count(),
                                                    Males = items.Count(),
                                                    UnSexed = items.Count(),
                                                    numberFromCurrentSet = items.Count()
                                                });


                        var group = await queryable.FirstOrDefaultAsync();

[smitpatel]

Currently EF Core doesn't expand navigations after a GroupBy operator is applied. You using IndividualObservation navigation hence it fails. As a work-around you can expand navigation manually using join before applying GroupBy and it should work.

Tracking issue: #22609

[fradsham]

I am not sure if I understand your response. I still need to use a .Join method even though I outlined the many-to-many relationship in the OnModelCreating method with the modelbuilder?

modelBuilder.Entity<IndividualObservation>(entity =>
            {
                entity.HasKey(x => x.GUID);
                entity.HasMany(x => x.ObservationGroups)
                       .WithOne(e => e.IndividualObservation)
                       .HasForeignKey(e => e.IndividualObservationGuid);
            });
modelBuilder.Entity<ObservationGroup>(entity =>
            {
                entity.HasKey(x => x.GUID);
                entity.HasIndex(x => x.GUID);
                entity.HasMany(x => x.IndividualObservations)
                       .WithOne(e => e.ObservationGroup)
                       .HasForeignKey(e => e.ObservationGroupGuid);
            });
modelBuilder.Entity<IndividualObservationGroup>(entity =>
            {
                entity.HasKey(io => new { io.IndividualObservationGuid, io.ObservationGroupGuid });
            });

[smitpatel]

Yes, you would need to use Join inside your query to manually join rather than using navigation. EF Core expands navigation in query to join for you. Doing that in queries which have GroupBy is being tracked by the issue I linked earlier.