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,
        ...
    };
}

1. This is feasible, there are a lot of libraries available which you could leverage to avoid needing to map your properties manually. Look at nuget.org/packages/AutoMapper or nuget.org/packages/Mapster 2. Not too sure why you can't, what error are you seeing? — timothyclifford
– timothyclifford, Commented Nov 19, 2015 at 14:20
You can avoid all of this and just use .Select(x => new MyTableDto { ID = x.ID, Name = x.Name }), until you execute the query with something like ToList() you are still only working with the query and doing a select on just those fields not all of them. — Stephen Brickner
– Stephen Brickner, Commented Nov 19, 2015 at 14:32
timothy: thanks, see answer below ... Stephen: the problem is, I don't want to do that in every method where I return an entity. I want to have the transformation only once per entity. — Pinzi
– Pinzi, Commented Nov 19, 2015 at 19:14

ocuenca · Accepted Answer · 2015-11-19 19:29:17Z

6

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.

edited Nov 19, 2015 at 19:29

answered Nov 19, 2015 at 14:23

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pinzi Over a year ago

Thanks, that's working perfectly. (I had to add a AsQueryable() to query.Select(selectExpr). Is it possible to create a generic ToDto for all my entities? (As with the lamda expression the mapping is already defined)

Pinzi Over a year ago

I had a look at Automapper. The problem is our database columns are mostly having german names and I don't want to have german field names in the dtos. Therefore I would still have to map each field seperatley

Pinzi Over a year ago

thank you, I'll have a further look at Automapper and how we can use it in the best way. I added an update to my question as it still fits the topic and I need to find the best possible solution for our problems.

Eakan Gopalakrishnan Over a year ago

This was very useful. I did it, but my team asked me: if my entity has a couple of LARGE varchar fields which I don't need to expose, when ToDTO is called, does it only fetch the fields that it really need explicitly? or is every field of the entity still fetched before the entity is converted to its corresponding DTO?

Erik Philips · Accepted Answer · 2015-11-19 19:34:47Z

0

I'd also add that if you intend to only use these as DTO, then you should be using the .AsNoTracking() before you enumerate from the SQL source.

Returns a new query where the entities returned will not be cached in the DbContext or ObjectContext.

answered Nov 19, 2015 at 19:34

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

Collectives™ on Stack Overflow

Entity Framework Data Transfer Objects Best Practice

UPDATE

2 Answers 2

Update

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

2 Answers 2

Update

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related