joelgullander Posts - Answer Overflow

joelgullander

•Created by joelgullander on 9/17/2024 in #help

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!

2 replies

Gaming

Programming