Entity Framework Data Transfer Objects Best Practice

Question

We have to use data transfer objects for many of our tables as they are very big and many columns aren't useful for the context I'm working.

To get the best performance I cannot read the complete database entities and covert it to dtos afterwards. Therefore I created a linq extension method to convert it to dtos before executing the query.

Call of extension method:

db.MyTable.Select(...).ToDto().ToList();

My extension method:

public static IQueryable<MyTableDTO> ToDto(this IQueryable<MyTable> query)
{
     return query.Select(x => new MyTableDTO
     {
         ID = x.ID,
         Name = x.Name
     });
}

Is this a feasable solution or are there better practises to do that?

Second question: There aren't only IQueryable< MyTable > objects which needs to be transformed to dtos, also MyTable objects have to be transformed. I created an extension method for MyTable class:

public static MyTableDto ToDto (this MyTable x)
{
    return new MyTableDto
    {
        ID = x.ID,
        Name = x.Name
    };
}

Why can't I use this function in my first ToDto function? Like:

public static IQueryable<MyTableDTO> ToDto(this IQueryable<MyTable> query)
{
    return query.Select(x => x.ToDto());
}

UPDATE

A further question because of the research below. There are also cases where we want to return only a minimum of fields for high performance issues.

It is possible to create a repository class where you can define a parameter to pass a Func with the fields which should be returned by the query (as described below). Then it is possible to create a class (MyServiceClass in the example below) where you can call the same repository method with for different return entities. But is that a good practise or what would be a better solution for it?

public class MyTableRepository<T>
{
    public List<T> GetMyTable(String search1, String search2, Func<MyTable, T> selectExp)
    {
        using(var db = new Context())
        {
            return db.MyTable.Where(x => x.A == search1 ...).Select(selectExp).ToList();
        }
    }
}

public class MyServiceClass
{
    public List<MyTableEntitySimple> GetMyTableEntitySimple(String  search1...)
    {
        MyTableRepository<MyTableEntitySimple> rep = new ...
        return rep.GetMyTable(search1, ToMyTableEntitySimple);
    }

    public List<MyTableEntity> GetMyTableEntity(String search1...)
    {
        MyTableRepository<MyTableEntity> rep = new ...
        return rep.GetMyTable(search1, ToMyTableEntity);
    }

    Func<MyTable, MyTableEntitySimple) ToMyTableEntitySimple = x => new MyTableEntitySimple
    {
        ID = x.ID,
        Name = x.Name
    };

    Func<MyTable, MyTableEntity) ToMyTableEntity = x => new MyTableEntitySimple
    {
        ID = x.ID,
        Name = x.Name,
        Field3 = x.Field3,
        Field4 = x.Field4,
        ...
    };
}

Answer 1

Because your Linq to Entities provider doesn't know how to translate your method call into a SQL statement. As a solution to your issue, you can use a lambda expression instead of an extension method:

Func<MyTable, MyTableDTO> selectExp=x => new MyTableDTO{
                                                         ID = x.ID,
                                                         Name = x.Name
                                                        });

//Pass the lambda expression as a paremter
public static IQueryable<MyTableDTO> ToDto(this IQueryable<MyTable> query, Func<MyTable, MyTableDTO> selectExpr)
{
    return query.Select(selectExpr);
}

Or as suggested @Timothy in his comment you can also use Automapper. Once you have mapped your entity class with its DTO, you can do something like this:

using AutoMapper.QueryableExtensions;

public static IQueryable<MyTableDTO> ToDto(this IQueryable<MyTable> query)
{
    return query.ProjectTo<MyTableDTO>();
}

You can find more info in this page.

Update

Well for my fist solution maybe you can create a generic extension method:

 public static IQueryable<T> ToDto<TSource,T>(this IQueryable<TSource> query, Func<TSource, T> selectExpr)
 {
    return query.Select(selectExpr);
 }

About the second one, which IMHO I'm still thinking is better for you, you can configure your mapping:

// Configure AutoMapper
Mapper.CreateMap<MyTable, MyTableDTO>()
    .ForMember(dest => dest.YourNewName1, opt => opt.MapFrom(src => src.YourGermanName1))
    .ForMember(dest => dest.YourNewName2, opt => opt.MapFrom(src => src.YourGermanName2));

You can find an great article about this subject in this link.