C
C#•4mo ago
joelgullander

Help with Entity Framework Core: Union of Two Queries with Inherited Types Failing

Hi everyone, I’m working on a project using EF Core and AutoMapper with HotChocolate for GraphQL. I’m trying to combine two queries using Union, but I'm encountering an error. Scenario: I have two inherited types: AppointmentInvitationEntity and NonAppointmentInvitationEntity, both inheriting from InvitationEntity. I need to query both types, filter them based on identifier and systemType, and combine the results into a single IQueryable<InvitationModel> using Union. Here’s my current code: 
[UsePaging]
[UseProjection]
[UseFiltering]
[UseSorting]
public async Task<IQueryable<InvitationModel>> GetInvitationsAsync(
    [Service] IOptions<FeatureFlagsOptions> featureFlags,
    [Service] IMapper mapper,
    ClaimsPrincipal claimsPrincipal,
    [Service] ApplicationDbContext dbContext,
    string identifier,
    SystemType systemType)
{
    var appointmentFilteredInvitations = dbContext.Invitations
        .OfType<AppointmentInvitationEntity>()
        .Where(inv => 
            inv.Patient!.OID.Identifier.Value == identifier &&
            inv.Patient!.OID.Identifier.System == systemType)
        .ProjectTo<InvitationModel>(mapper.ConfigurationProvider);

    var nonAppointmentFilteredInvitations = dbContext.Invitations
        .OfType<NonAppointmentInvitationEntity>()
        .Where(inv =>
            inv.Patient.OID.Identifier.Value == identifier &&
            inv.Patient.OID.Identifier.System == systemType)
        .ProjectTo<InvitationModel>(mapper.ConfigurationProvider);

    var combinedInvitations = appointmentFilteredInvitations
        .Union(nonAppointmentFilteredInvitations);

    return combinedInvitations;
}
[UsePaging]
[UseProjection]
[UseFiltering]
[UseSorting]
public async Task<IQueryable<InvitationModel>> GetInvitationsAsync(
    [Service] IOptions<FeatureFlagsOptions> featureFlags,
    [Service] IMapper mapper,
    ClaimsPrincipal claimsPrincipal,
    [Service] ApplicationDbContext dbContext,
    string identifier,
    SystemType systemType)
{
    var appointmentFilteredInvitations = dbContext.Invitations
        .OfType<AppointmentInvitationEntity>()
        .Where(inv => 
            inv.Patient!.OID.Identifier.Value == identifier &&
            inv.Patient!.OID.Identifier.System == systemType)
        .ProjectTo<InvitationModel>(mapper.ConfigurationProvider);

    var nonAppointmentFilteredInvitations = dbContext.Invitations
        .OfType<NonAppointmentInvitationEntity>()
        .Where(inv =>
            inv.Patient.OID.Identifier.Value == identifier &&
            inv.Patient.OID.Identifier.System == systemType)
        .ProjectTo<InvitationModel>(mapper.ConfigurationProvider);

    var combinedInvitations = appointmentFilteredInvitations
        .Union(nonAppointmentFilteredInvitations);

    return combinedInvitations;
}
The Problem: When I run this code, I get the following error: 
Unable to translate set operation since both operands have different 'Include' operations. Consider having same 'Include' applied on both sides.
Unable to translate set operation since both operands have different 'Include' operations. Consider having same 'Include' applied on both sides.
I’ve tried removing ProjectTo, but that leads to other issues with how the two entity types are combined. EF Core seems to have trouble combining two queries with different navigation properties, even though I’m using ProjectTo to project both into the same InvitationModel. My Question: 1. Why does this fail with the error about 'Include' operations even though I’m using ProjectTo to project both queries into the same type? 2. Is there a better approach to combine these two queries at the database level without loading everything into memory? Thanks in advance for any help or insights!
1 Reply
joelgullander
joelgullanderOP•4mo ago
      "extensions": {
        "message": "Unable to translate set operation since both operands have different 'Include' operations. Consider having same 'Include' applied on both sides.",
        "stackTrace": "   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ValidateExpressionCompatibility(Expression outer, Expression inner)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessSetOperation(NavigationExpansionExpression outerSource, MethodInfo genericMethod, NavigationExpansionExpression innerSource)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)\n   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\n   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()\n   at HotChocolate.Types.Pagination.QueryableCursorPagination`1.ExecuteAsync(IQueryable`1 query, Int32 offset, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.CursorPaginationAlgorithm`2.ApplyPaginationAsync(TQuery query, CursorPagingArguments arguments, Nullable`1 totalCount, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.QueryableCursorPagingHandler`1.ResolveAsync(IResolverContext context, IQueryable`1 source, CursorPagingArguments arguments, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.CursorPagingHandler.HotChocolate.Types.Pagination.IPagingHandler.SliceAsync(IResolverContext context, Object source)\n   at HotChocolate.Types.Pagination.PagingMiddleware.InvokeAsync(IMiddlewareContext context)\n   at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)\n   at HotChocolate.Authorization.AuthorizeMiddleware.InvokeAsync(IMiddlewareContext context)\n   at HotChocolate.Authorization.AuthorizationTypeInterceptor.<>c__DisplayClass28_1.<<CreateAuthMiddleware>b__1>d.MoveNext()\n--- End of stack trace from previous location ---\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
      "extensions": {
        "message": "Unable to translate set operation since both operands have different 'Include' operations. Consider having same 'Include' applied on both sides.",
        "stackTrace": "   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ValidateExpressionCompatibility(Expression outer, Expression inner)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessSetOperation(NavigationExpansionExpression outerSource, MethodInfo genericMethod, NavigationExpansionExpression innerSource)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\n   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)\n   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\n   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()\n   at HotChocolate.Types.Pagination.QueryableCursorPagination`1.ExecuteAsync(IQueryable`1 query, Int32 offset, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.CursorPaginationAlgorithm`2.ApplyPaginationAsync(TQuery query, CursorPagingArguments arguments, Nullable`1 totalCount, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.QueryableCursorPagingHandler`1.ResolveAsync(IResolverContext context, IQueryable`1 source, CursorPagingArguments arguments, CancellationToken cancellationToken)\n   at HotChocolate.Types.Pagination.CursorPagingHandler.HotChocolate.Types.Pagination.IPagingHandler.SliceAsync(IResolverContext context, Object source)\n   at HotChocolate.Types.Pagination.PagingMiddleware.InvokeAsync(IMiddlewareContext context)\n   at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)\n   at HotChocolate.Authorization.AuthorizeMiddleware.InvokeAsync(IMiddlewareContext context)\n   at HotChocolate.Authorization.AuthorizationTypeInterceptor.<>c__DisplayClass28_1.<<CreateAuthMiddleware>b__1>d.MoveNext()\n--- End of stack trace from previous location ---\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
Want results from more Discord servers?
Add your server