How to efficiently handle Where and OrderBy clauses

My business layer passes all the required information to UI layer. From what I have read, in general, best practice is to send fetched data to UI layer, and to avoid passing queries like ObjectQuery. My problem with this approach is next:

If I am to make a flexible business layer, then I should allow UI to sort the data anyway it requires. Fetching sorted data from database, and then resorting them in UI is kind of bad practice for me, so the only way is to somehow

So what are my options? Is there a 开发者_StackOverflow中文版way to make it like this:

public void OrderByMethod(params ...) { .... }

so I can call it like this:

OrderByMethod(MyEntity.Property1, MyEntity.Property2 descending....);

Thanks, Goran

If you do not expose IQueryable directly to UI (you probably don't because you are using business layer between UI and data access layer) you can use custom object passing "list information". It can look like:

public class ListOptions<T>
{
    // Paging
    public int Page { get; set; }
    public int PageSize { get; set; }
    // Sorting
    public IList<SortOptions<T>> SortOptions { get; set; }
}

public class SortOptions<T>
{
    public Expression<Func<T, object>> SortProperty { get; set; }
    public bool IsDescending { get; set; }
}

You will use this as parameter of your business method returning data and inside business method you will use custom extensions to work with IQueryable provided by EF or repository:

public static class QueryableExtensions
{
    public static IQueryable<T> ApplyListOptions<T>(this IQueryable<T> query, ListOptions<T> options)
    {
        if (options != null && options.SortOptions.Count > 0)
        {
            IOrderedQueryable<T> orderedQuery = query.ApplyOrderBy(options.SortOptions[0]);

            for (int i = 1; i < options.SortOptions.Count; i++)
            {
                orderedQuery = orderedQuery.ApplyThenBy(options.SortOptions[i]);
            }

            query = orderedQuery.ApplyPaging(options.Page, options.PageSize);
        }

        return query;
    }

    public static IOrderedQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, SortOptions<T> sortOption)
    {
        if (sortOption.IsDescending)
        {
            return query.OrderByDescending(sortOption.SortProperty);
        }

        return query.OrderBy(sortOption.SortProperty);
    }

    public static IOrderedQueryable<T> ApplyThenBy<T>(this IOrderedQueryable<T> query, SortOptions<T> sortOption)
    {
        if (sortOption.IsDescending)
        {
            return query.ThenByDescending(sortOption.SortProperty);
        }

        return query.ThenBy(sortOption.SortProperty);
    }

    public static IQueryable<T> ApplyPaging<T>(this IOrderedQueryable<T> query, int page, int pageSize)
    {
        if (pageSize > 0)
        {
            return query.Skip((page - 1)*pageSize).Take(pageSize);
        }

        return query;
    }
}

So your processing method can look like:

public IEnumerable<User> GetUsers(ListOptions<User> listOptions)
{
    return _context.Users.ApplyListOptioins(listOptions).AsEnumerable();
}

And you will call the method like:

var options = new ListOptions<User>
    {
        Page = 2,
        PageSize = 3,
        SortOptions = new List<SortOptions<User>>
            {
                new SortOptions<User> 
                    { 
                        IsDescending = false, 
                        SortProperty = u => u.LastName 
                    },
                new SortOptions<User> 
                    { 
                        IsDescending = true, 
                        SortProperty = u => u.FirstName 
                    }
            }
    };


var data = usersService.GetUsers(options);        

How is the data being passed from one layer to another? It sort of sounds like you're using a simple repository pattern, is that correct? If so, what do the repository methods generally return?

It's often good practice for the data layer and business layer (assuming no hard separations, such as a web service layer) to pass data along in the form of an IEnumerable<T> or IQueryable<T>. That way when the data gets to the UI it's very easy for the UI code to continue to manipulate the data as needed (sorting, paging, etc.).

In fact, if the actual data isn't enumerated until it gets to the UI, then it shouldn't even make the database request until that time. This allows for such things as passing an IEnumerable<T> of an entire result set logically to the UI and then having the UI determine that it needs only the first "page" of records (by calling .Take() and grabbing only the first 10, for example). That way you can use a single very simple repository method in lots of different places throughout the application, bleeding less business and display logic into the data layer.