OrderBy when using DTO in N-Tier app

Question

I have a tiered app written in C# -

Front end - (Get pages of Customers)

Business - (CustomerService and Customer class)

DataContracts - (CustomerDTO)

DataAccess - (UnitOfWork and Repositories)

The database has a Customer table with over a hundred columns (many redundant), so I'm using a DTO to populate a Customer object at the business layer. In the Customer class I have changed the names of many of the fields from their database names too, e.g. ID to CustomerID, FName to Firstname.

The front end uses services at the business layer to get Customers, e.g. GetAll(), GetByID(int customerID).

I also want to offer a GetPaged method like below.

CustomerService class

public IEnumerable<Customer> GetPaged(
               Func<IQueryable<Customer>, IOrderedQueryable<Customer>> orderBy, 
               int skip, 
               int take)
{
     foreach (var customerDTO in 
                     unitOfWork.CustomerRepository.GetPaged(orderBy, skip, take))
     {
          yield return new Customer(customerDTO);
     }
}

But this will not work because the CustomerRepository is expecting an orderBy based on the CustomerDTO not the Customer.

I don't want the front end to know anything about the CustomerDTO.

How can I achieve this?

A way to get this working would be to use a dynamic OrderBy like [here](http://stackoverflow.com/questions/41244/dynamic-linq-orderby), so you can parametrize the ordering by column names (strings) and maybe map them to the names used in the data layer. — Gert Arnold, Feb 20 '13 at 19:54
I have tried a number of approaches, seems that a static constructor filling a dictionary with strings representing the mapping and a translation method is the only viable approach. — tom, Feb 21 '13 at 16:39
Don't be so sure, take a look at my simpler answer. You don't need to use a dynamic OrderBy to complicate things more. — kzfabi, Mar 05 '13 at 17:38
@FabianFernandez the drawback to your solution is for every property, you'll need to create XXXAsc and XXXDesc and add it to the switch statement. While it's simple to understand, it falls flat when it comes to maintainability. — Chuck Conway, Mar 05 '13 at 17:48
@ChuckConway I know that. But I also know that one complex line doesn't mean it is more maintainable. Know that using the dynamic aproach as Gert Arnold said, you would need a string to be passed to the method in order to be used by the dynamic OrderBy. Is that string value maintenable? Who garantizes that every one who uses that method will pass the value in the correct format and the correct option? Using an enum reduces the play field to just what is accepted. Maybe if using the enum and then translate the enum value into the string format needed by the dynamic OrderBy could do the work. — kzfabi, Mar 05 '13 at 17:56
@FabianFernandez I'll take 1 line of code over 20 lines of code any day, even when the 1 line is not easy to understand for the layman programmer. — Chuck Conway, Mar 05 '13 at 18:11
@FabianFernandez Ideally the strings will be derived from the property name or an attribute hanging on off the property name. As for passing the correct data into the method, there is no guarantee that someone will pass the correct data into the method. This issue would be caught either by tests or the database barfing. — Chuck Conway, Mar 05 '13 at 18:16
@ChuckConway if you think [this](http://stackoverflow.com/a/233505/859461) (which is what Gert suggested) is less complex, has LESS lines of code and is more maintainable, I guess you didn't even take a look at it. Besides, 1 line of code over 20 when 1 is not understandable and takes you minutes to get the idea against 20 lines that explain by theirself, I guess you neither read [Clean Code](http://amzn.com/0132350882). — kzfabi, Mar 05 '13 at 18:18
@FabianFernandez Don't take it personal. I did not look at the code linked by Gert. I have written dynamic order by code, it wasn't more than 5 or 6 lines of code. — Chuck Conway, Mar 05 '13 at 18:41

score 0 · Answer 1 · answered Mar 05 '13 at 17:35

You could expose an enum with the available order options, then in GetPaged you just create the OrderBy based on the enum value received.

Possible implementation could be:

public enum OrderCustomersBy
{
    FirstNameAsc, FirstNameDesc, AgeAsc, AgeDesc
}

And in CustomerService:

private IOrderedQueryable<CustomerDTO> GetOrderBy(OrderCustomersBy orderOption)
{
    IOrderedQueryable<CustomerDTO> orderBy = null;

    switch (orderOption)
    {
        case OrderCustomersBy.FirstNameAsc: 
            // Create order by...
            break;
        case OrderCustomersBy.FirstNameDesc: 
            // Create order by...
            break;
        case OrderCustomersBy.AgeAsc: 
            // Create order by...
            break;
        case OrderCustomersBy.AgeDesc: 
            // Create order by...
            break;
        default:
            throw new NotImplementedException("Order option not implemented: " + orderOption.ToString());
    }

    return orderBy;
}

public IEnumerable<Customer> GetPaged(Func<IQueryable<Customer>> func, OrderCustomersBy orderOption, int skip, int take)
{
    IOrderedQueryable<CustomerDTO> orderBy = this.GetOrderBy(orderOption);

    foreach (var customerDTO in unitOfWork.CustomerRepository.GetPaged(orderBy, skip, take))
    {
        yield return new Customer(customerDTO);
    }
}

Chuck Conway · Answer 2 · 2013-03-05T20:28:31.590

I see two issues: First, how do you map the business entity to the DTO's and second, how do you perform an order-by on any column?

Mapping the Business Entity to the DTOs. This can be done a number of ways. To list a few: AutoMapper could be used, you could do a simple mapping using attributes (the attribute would contain the property name of the customDTO or the database column (using the database column might negate the value of the DTOs)) or you could drive it from the database, the database approach is a bit heavy handed and meta.

I would use dynamic order by as suggested in the comments. Yes it does add a layer of abstraction, but it reduces maintenance.

Dynamic Order By

    public static IQueryable<T> OrderBy<T>(this IQueryable<T> items, string propertyName, SortDirection direction)
    {
        var typeOfT = typeof(T);
        var parameter = Expression.Parameter(typeOfT, "parameter");
        var propertyType = typeOfT.GetProperty(propertyName).PropertyType;
        var propertyAccess = Expression.PropertyOrField(parameter, propertyName);
        var orderExpression = Expression.Lambda(propertyAccess, parameter);

        string orderbyMethod = (direction == SortDirection.Ascending ? "OrderBy" : "OrderByDescending");
        var expression = Expression.Call(typeof(Queryable), orderbyMethod, new[] { typeOfT, propertyType }, items.Expression, Expression.Quote(orderExpression));
        return items.Provider.CreateQuery<T>(expression);
    }

At the end of the day when I change an entity, I am not interested in changing code in another places in the application.

I agree, mostly because of the last part. – kzfabi Mar 05 '13 at 18:36 — kzfabi, Mar 05 '13 at 18:36

OrderBy when using DTO in N-Tier app

2 Answers2