"Where" clause after "ProjectTo" and Nullable types?

Question

Working on a REST API with an ASP.NET Core 5 and EF Core 5 backend.

I got the following entities, DTOs and mappings (only including the relevant code):

// Entities
//
class Book {
  public int Id { get; set; }
  public string Title { get; set; }
  public int AuthorId { get; set; }   // foreing-key
  public Author Author { get; set; }  // nav prop
}

class Author {
  public int Id { get; set; }
  public string Name { get; set; }
}

(assume proper EF config to indicate HasOne/HasMany WithOne/WithMany accordingly)

// DTOs
//
class AuthorDTO { ... }

class BookDTO {
  ...
  AuthorDTO Author { get; set; }
}

// Automapper Maps
//
CreateMap<Book, BookDTO>();
CreateMap<Author, AuthorDTO>();

If I run the code below, life is good. In fact, any filtering done on the root-level fields of the BookDTO runs just fine:

var data = await dbContex
  .Books
  .ProjectTo<BookDTO>(mapper.ConfigurationProvider)
  .Where(bDto => bDto.Id == 4)
  .ToListAsync();

Filtering done on the nested AuthorDTO fields works as long as the field is a nullable or a reference type. Eg.

var data = await dbContex
  .Books
  .ProjectTo<BookDTO>(mapper.ConfigurationProvider)
  .Where(bDto => bDto.Author.Name == "John")
  .ToListAsync();

However, queries on non-nullable or value types fails:

var data = await dbContex
  .Books
  .ProjectTo<BookDTO>(mapper.ConfigurationProvider)
  .Where(bDto => bDto.Author.Id == 10)
  .ToListAsync();

Error:

"The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'."

If I'd declare the AuthorDTO Id as int?, the code works.

I've already tried several casting combinations within the Where clause.

Any suggestions would be welcome.

PS: None of these issues happen if I put the Where clause before the ProjectTo projection and do all filtering based on the entities fields instead of the DTO fields. For those of you wondering why I'm doing the filtering based on the DTO: I'm using Sieve, a package that allows me to do filtering and paging "a la OData", and the fields that the client receives when calling my API are those in the DTOs, so I really need to apply all queries after the ProjectTo.

Answer 1

Generally speaking, the problem is in EF Core, since AutoMapper ProjectTo just produces a Select, which you could do manually and still experience the same issue.

The problem is with nullable references. And by default AutoMapper assumes any reference type property allows null, hence the generated projection is something like this (note the conditional operator and null check):

Author = src.Author == null ? null : new AuthorDto
{
    Id = src.Author.Id,
    Name = src.Author.Name,
}

Once you have, any attempt to apply additional LINQ operator (Where, OrderBy etc.) on the resulting Author property non nullable member will hit the unpleasant EF Core bug/limitation (because for regular entity reference navigation properties w/o projection it's probably handled differently and somehow works).

With that being said, what's the solution? If the property is optional (allow null), there is nothing that can be done (actually there is an ugly workaround which requires manual navigation property expansion, which kills all the benefits of navigation properties and is not generally applicable to AutoMapper).

But if the properties are required (do not allow null, i.e. the source is coming from a required relationship), then the solution is simply to remove the conditional operator and just generate

Author = new AuthorDto
{
    Id = src.Author.Id,
    Name = src.Author.Name,
}

which with AutoMapper can be achieved in two ways. First is to change the default for all reference type properties

AllowNullDestinationValues = false; // Default is true

(there is similar property for collections called AllowNullCollections, which is also true by default, and makes sense to be set to false since in LINQ to Entities collections are never null)

Another approach is to leave the default as is and configure it separately for each property:

CreateMap<Book, BookDto>()
    .ForMember(dst => dst.Author, opt => opt.DoNotAllowNull());

Actually there is a third option - change the defaults and then use opt.AllowNull() to override it just for optional properties.

Just remember - whatever option you use, it won't work if you have optional reference property type until EF Core provides a resolution of the null projection problem.