Question posted in
The official documentation can be found here.

Asp.net – How to avoid duplication of code when trying to limit columns selected by EF?

Consider the following very big object stored in database being access via Entity Framework (EF Core 8):

public record VeryBigObject(int Id, string Name, string LotsOfData);

And a corresponding DTO:

public record VeryBigObjectDto(int Id, string Name);

In real life, there are a lot more columns than just Id and Name, but for simplicity’s sake, let’s stick to those two. I only very rarely need the LotsOfData column and would normally prefer returning a shallow copy without it, because I don’t want to transfer the data between the database and my application. Unfortunately it seems not possible to:

  1. limit the columns selected by EF from the database; and
  2. not duplicate the mapping code between my domain object and its dto

Consider the following service:

public class VeryBigObjectService(Db context) : IVeryBigObjectService
{
    
    public async Task<VeryBigObjectDto> FindById1(int id)
    {
        var obj = await context.VeryBigObjects
            .Where(o => o.Id == id)
            .Select(o => new VeryBigObjectDto(o.Id, o.Name))
            .FirstOrDefaultAsync();
        return obj;
    }
    
    public async Task<VeryBigObjectDto> FindById2(int id)
    {
        var obj = await context.VeryBigObjects
            .Where(o => o.Id == id)
            .Select(o => MapToDto(o))
            .FirstOrDefaultAsync();
        return obj;
    }

    private static VeryBigObjectDto MapToDto(VeryBigObject o) => new VeryBigObjectDto(o.Id, o.Name);

}

When FindById1 method is called, the resulting SQL is only selecting id and name as it should:

SELECT v.id, v.name
FROM very_big_objects AS v
WHERE v.id = @__id_0
LIMIT 1

Unfortunately, if multiple methods from my service want to return such a DTO, every single one of them needs a similar new VeryBigObjectDto(…) call (which gets very long, very fast) and code gets duplicated. It may not be bad in this case, but if you consider an object with 15+ columns and child relationships, it’s very bad.

If I try to avoid duplicating such code and implement a static method to map the object to DTO, as shown with FindById2, the resulting SQL will pull the big column in:

SELECT v.id, v.lots_of_data, v.name
FROM very_big_objects AS v
WHERE v.id = @__id_0
LIMIT 1

It’d get even worse if VeryBigObject had a relationship to something else, which I would also like to return as a reusable Dto, because then I have that ChildDto mapping copied across multiple places (ChildService and this VeryBigObjectService).

Is there any way not to duplicate code AND keep the big column from being fetched?

Tags:

2

Answers


  1. 0

    MapToDto is just a user-defined method and can’t be converted by EF into SQL so it leads to EF fetching the whole entity (since it can’t determine what actually is used). If you want shareable selectors then you will need to use expression trees. Something like the following:

    static Expression<Func<VeryBigObject, VeryBigObjectDto>> GetMapToDtoExpr() => (VeryBigObject o) 
   => new VeryBigObjectDto(o.Id, o.Name);

    But this will have limitation that it can’t use one such expression in another. This can be worked around by LINQKit’s AsExpandable – see the Combining Expressions section of the docs.

    Another very popular option is to use mapper library which allows building projection expression. For example AutoMapper and it’s Queryable Extensions which allows to use subset of specified mappings via ProjectTo() (note that there are some pitfalls – it can silently skip invalid mapping setup, for example see this question).

    Login or Signup to reply.
  2. 0

    In your example MapToDto is a function, so EF only knows to pull the entire entity from the database in order to construct an object to use as a parameter. Instead, you can use an Expression to reuse for EF queries:

    private static Expression<Func<VeryBigObject, VeryBigObjectDto>> MapToDto = vbo =>
new VeryBigObjectDto(vbo.Id, vbo.Name);

    Then use it in queries like this:

    public async Task<VeryBigObjectDto> FindById2(int id)
{
    var obj = await context.VeryBigObjects
        .Where(o => o.Id == id)
        .Select(MapToDto)
        .FirstOrDefaultAsync();
    return obj;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search