GridView ObjectDataSource LINQ Paging and Sorting using multiple table query.
- by user367426
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.
