EF Core Mapping Entities to Models

Question

I'm trying to efficiently map entities on to models.

My entities are:

public class ParentEntity
{
    public int Id { get; set; }
    public string  Name { get; set; }
    public ChildEntity Child { get; set; }
}

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

and my models are:

public class ParentModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ChildModel Child { get; set; }
}

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

(In practice, there would be differences between these classes, but not here for simplification.)

I've written an extension method to do the mapping:

public static IQueryable<ParentModel> ToParentModel (this IQueryable<ParentEntity> parentEntities)
{
    return parentEntities.Select(p => new ParentModel
    {
        Id = p.Id,
        Name = p.Name,
        Child = new ChildModel { Id = p.Child.Id, Name = p.Child.Name.ToLower()}
    });
}

The ToLower() is there to highlight the problem.

I can run this with:

var parents = _context.Set<ParentEntity>().ToParentModel().ToArray();

The generated SQL is:

 SELECT "p"."Id", "p"."Name", "c"."Id", lower("c"."Name") AS "Name"
 FROM "Parents" AS "p"
 LEFT JOIN "Children" AS "c" ON "p"."ChildId" = "c"."Id" 

i.e. the lowercase processing is done in the database.

All good so far, except that the separation of concerns is not good. The code to initialize a ChildModel is in the same place as the code to initialize a ParentModel.

I try using a constructor in ChildModel:

public ChildModel(ChildEntity ent)
{
    Id = ent.Id;
    Name = ent.Name.ToLower();
}

and in the extension method:

return parentEntities.Select(p => new ParentModel
{
    Id = p.Id,
    Name = p.Name,
    Child = new ChildModel (p.Child)
});

This works, but the generated SQL does not do contains a lower. The conversion to lowercase is done in the program.

Is there a way I can have by cake and eat it?

Can I still have my C# code converted to SQL, but still structure my C# code in a modular way?