GridView ObjectDataSource LINQ Paging and Sorting using multiple table query.

Question

I am trying to create a pageing and sorting object data source that before execution returns all results, then sorts on these results before filtering and then using the take and skip methods with the aim of retrieving just a subset of results from the database (saving on database traffic). this is based on the following article:

http://www.singingeels.com/Blogs/Nullable/2008/03/26/Dynamic_LINQ_OrderBy_using_String_Names.aspx

Now I have managed to get this working even creating lambda expressions to reflect the sort expression returned from the grid even finding out the data type to sort for DateTime and Decimal.

public static string GetReturnType<TInput>(string value)
        {
            var param = Expression.Parameter(typeof(TInput), "o");
            Expression a = Expression.Property(param, "DisplayPriceType");
            Expression b = Expression.Property(a, "Name"); 
            Expression converted = Expression.Convert(Expression.Property(param, value), typeof(object));
            Expression<Func<TInput, object>> mySortExpression = Expression.Lambda<Func<TInput, object>>(converted, param);
            UnaryExpression member = (UnaryExpression)mySortExpression.Body;
            return member.Operand.Type.FullName;
        }

Now the problem I have is that many of the Queries return joined tables and I would like to sort on fields from the other tables.

So when executing a query you can create a function that will assign the properties from other tables to properties created in the partial class.

public static Account InitAccount(Account account)
        {
            account.CurrencyName = account.Currency.Name;
            account.PriceTypeName = account.DisplayPriceType.Name;
            return account;
        }

So my question is, is there a way to assign the value from the joined table to the property of the current table partial class? i have tried using.

from a in dc.Accounts
                                 where a.CompanyID == companyID
                                 && a.Archived == null
                                 select new { 
PriceTypeName = a.DisplayPriceType.Name})

but this seems to mess up my SortExpression.

Any help on this would be much appreciated, I do understand that this is complex stuff.

Answer 1

This is a functional programming thing. Mutating Account by doing an assignment is out. New-ing up a new instance of the shape you want is in.

Step 1: declare a class that has the shape of the result you want:

public class QueryResult
{
  public int CompanyID {get;set;}
  public string CurrencyName {get;set;}
  public string PriceTypeName {get;set;}
}

Step 2: project into that class in your query

from ...
where ...
select new QueryResult()
{
  CompanyID = a.CompanyID,
  CurrencyName = a.Currency.Name,
  PriceTypeName = a.PriceType.Name
};

Step 3: Profit! (order by that)

The query generator will use the details of your QueryResult type to generate a select clause with that shape.