AutoMapper EF Core: Query where nested property equals value-CodePudding

Seems like a simple one but I couldn't get the following query to execute with EF core.

public class ParentThing // EF entity
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int? ChildThingId { get; set; }
   public ChildThing Child { get; set ; }
}

public class ChildThing // EF entity
{
   public int Id { get; set; }
   public string Name { get; set; }
}

public class ChildDTO 
{
   public int Id { get; set; }
}

public class ParentDTO
{
   public string Name { get; set; }
   public ChildDTO Child { get; set; }
}

public static void Run(int id, DbSet<ParentThing> dbSet, IMapper mapper)
{
   // mappings are something like this...
   // CreateMap<ChildThing, ChildDTO>();
   // CreateMap<ParentThing, ParentDTO>();

   var dtos = mapper.ProjectTo<ParentDTO>(dbSet)
               .Where(e => e.Child.Id == id) // this throws an exception
               .ToList();
}

I get the following exception when trying to execute the query.

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

I tried converting the queried id to a nullable int and got the following exception.

System.InvalidOperationException: Rewriting child expression from type 'System.Int32' to type 'System.Nullable`1[System.Int32]' is not allowed, because it would change the meaning of the operation. If this is intentional, override 'VisitUnary' and change it to allow this rewrite.

Do I really need to flatten out the nested properties in order to query them?

CodePudding user response：

Do I really need to flatten out the nested properties in order to query them?

Looks like you need to.

The whole question is whether the "child" reference can be null or not (i.e. optional or required).

If it is optional, there is no other solution than flattening the reference object properties and making them nullable. Or apply the filter on the entity query before the projection. This is because EF Core handles navigation properties differently (and better) than projections. What AutoMapper is doing is equivalent of something like

db.Set<ParentThing>()
    .Select(p => new ParentDto
    {
        // ...
        Child = p.ChildThing == null ? null : new ChildDto { ... })
    }
    .Where(...);

The error you are getting is because of the conditional construct. But if you don't use it, then you'll get different exception when the data is null.

So, a clean solution is only possible when the reference is required. In that case, all you need is to let AutoMapper eliminate the null check and generate directly Child = new ChildDto { ... }. Which can be specified with DoNotAllowNull, e.g.

CreateMap<ParentThing, ParentDTO>()
    .ForMember(dst => dst.Child, opt => opt.DoNotAllowNull());

But again, this works only for required references.

CodePudding user response：

Since you are using

public ChildThing Child { get; set ; }

it can confuse entity framework

So I think to create explicit navigaion properties could help

public class ParentThing // EF entity
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int? ChildThingId { get; set; }
   public ChildThing ChildThing { get; set ; }
}

public class ChildThing // EF entity
{
   public int Id { get; set; }
   public string Name { get; set; }

    public string Name { get; set; }
    public ParentThing ParentThing { get; set ; }
}

I am wondering if your DbSet<> includes Child, it can be null and it could cause an exception. But in this case you can use this code

 var parents=  dbSet
         .Where(e => e.ChildThingId!=null && ( (int) e.ChildThingId ) == id)
         .ToList();

 var dtos = mapper.Map<List<ParentThing>, List<ParentDto>>(parents);