How do I correctly use EF Core with AutoMapper ProjectTo and Unions?

Question

My Setup

• ASP.NET Core 2.0
• EntityFrameworkCore 2.0.1
• AutoMapper 6.2.2

---

Problem

I have a project with a DTO called PersonDetail and an Entity called Person. When I call

db.People.Where(p => p.FirstName == "Joe").Union(db.People.Where(p => Age > 30)).ProjectTo<PersonDetail>(mapperConfig).ToList(); 

I do not get the PersonDetail DTOs and Entity Framework (Core) throws an exception with the message:

ArgumentException: The input sequence must have items of type 'Test.Module.Entities.Person', but it has items of type 'Test.Module.Dtos.PersonDetail'.

---

Example without the problem

When I run the code:

 db.People.Where(p => p.FirstName == "Joe").Union(db.People.Where(p => Age > 30)).ToList(); 

I get the Person entities with no exceptions.

---

The Execution Plans

Here is a working plan (with a union):

{value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Test.Module.Entities.Person]).Where(entity => ((entity != null) And ((63ed0ebd-2c02-4496-ac8d-b836cbf13259 == entity.CreatedBy) Or (393a6bb0-b437-4664-beb0-6800f509451b == entity.CreatedBy)))).Union(value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Test.Module.Entities.Person]))}

Now here is the same plan but with automapper projections too:

{value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Test.Module.Entities.Person]).Where(entity => ((entity != null) And ((63ed0ebd-2c02-4496-ac8d-b836cbf13259 == entity.CreatedBy) Or (393a6bb0-b437-4664-beb0-6800f509451b == entity.CreatedBy)))).Union(value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Test.Module.Entities.Person])).Select(dto => new PersonDetail() {FirstName = dto.FirstName, LastName = dto.LastName, Deleted = dto.Deleted, Age = dto.Age, CreatedUtc = dto.CreatedUtc, CreatedBy = dto.CreatedBy, Id = dto.Id, RecordVersion = dto.RecordVersion, DisplayLabel = ((dto.FirstName + " ") + dto.LastName)})}

---

Note:

I'm only calling ToList to reduce this problem to it's smallest form. I understand that this doesn't seem like I need to use ProjectTo in this example. In my actual code, we are using OData and we need the final result to be a projected query with the DTOs as Queryable objects. I also understand that this Union is not really a good union example, bu again, just for simplification of the Union problem.

Ia also opened issues on the respective GitHub projects:

EntityFrameworkCore: https://github.com/aspnet/EntityFrameworkCore/issues/11033

AutoMapper: https://github.com/AutoMapper/AutoMapper/issues/2537

Answer 1

It was an EF Core bug and it has been fixed in EF Core 2.1 https://github.com/aspnet/EntityFrameworkCore/issues/11033

Answer 2

It's difficult to tell exactly what is going wrong without more details, but make sure your mapping is correct, e.g. if using mapping profiles

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        CreateMap<Person, PersonDetail>();
    }
}

And assuming your EF context has a set:

public virtual DbSet<Person> People { get; set; }

Then you should be able to query the context and project as below:

var details = _context.People
    .Where(p => p.LastName == 'Smith')
    .OrderBy(p => p.FirstName)
    .ProjectTo<PersonDetail>
    .ToList();

You don't need AsNoTracking as EF does not track result types that are not entities, see the docs on Tracking and projections

--- UPDATE ---

The following should work, although EF Core will evaluate it in memory:

var firstNameQuery = db.People
    .Where(p => p.FirstName == "Joe")
    .ProjectTo<PersonDetail>(mapperConfig);
var ageQuery = db.People
    .Where(p => p.FirstName == "Joe")
    .ProjectTo<PersonDetail>(mapperConfig);
var results = firstNameQuery.Union(ageQuery).ToList();