Linq To Sql - Changing Sort Order At Run-Time with well known static typing

Question

This is not another question about 'How Can I Sort Dynamically (based on an arbitrary user provided field)?'

The question is -- how can I change sort order when I know the potential sorts in advance? (And thus avoid reflection / custom Expression building typically associated with truly dynamic sorting.)

Take for instance this subquery (shortened for this example) of a larger query:

(from solutionIds in context.csExtendedQAIncident_Docs
    where solutionIds.tiRecordStatus == 1
    && (from solutionProductAssocation in context.csProductDocs
           where solutionProductAssocation.iSiteId == Settings.Current.WebUtility().Onyx.SiteId
           && (from allowedProduct in context.KB_User_Allowed_Products
                  where allowedProduct.UserId == userId
                  select allowedProduct.ModelCode
               ).Contains(solutionProductAssocation.chModelCd)
           select solutionProductAssocation.chIdNo).Distinct().Contains(solutionIds.chIdNo)
).OrderByDescending(s => s.dtUpdateDate)
.Select(s => s.chIdNo)
.Take(count ?? Settings.Current.WCFServices().Output.HomePage.MaxRows)

The OrderByDescending portion works as I would expect.

Now -- I want to factor that out like the following:

Expression<Func<csExtendedQAIncident_Doc, IComparable>> ordering =  (s) => s.dtUpdateDate;
if (viewType == HomepageViewType.MostViewed)
   ordering = (s) => s.vchUserField8;
else if (viewType == HomepageViewType.MostEffective)
   ordering = (s) => s.vchUserField4;

and then use:

OrderByDescending(ordering)

This does compile, but blows up at run-time.

Unsupported overload used for query operator 'OrderByDescending'.

This of course comes from deep in the bowels of System.Data.Linq.SqlClient.QueryConverter -- in particular VisitSequenceOperatorCall. Reflectoring that code reveals that the following conditions must be met for OrderByDescending to properly evaluate. 'mc' is the MethodCallExpression passed into the method.

if (((mc.Arguments.Count != 2) || !this.IsLambda(mc.Arguments[1]))
    || (this.GetLambda(mc.Arguments[1]).Parameters.Count != 1))
{
   break;
}

So essentially that MethodCallExpression has to have 2 arguments, the second of which has to be a Expressions.LambdaExpression with a single parameter (presumably the sort field). If that code breaks out, the exception that I got is thrown.

So clearly I have not constructed the expression correctly. Without digging in any further here, does anyone know how to correctly construct the sorting Expression?

Answer 1

I believe that this will not work unless the two possible fields have the identical type.

Then the linq to sql will (if possible) correctly create the relevant sql.

so for example if both of those fields were DateTimes:

Expression<Func<csExtendedQAIncident_Doc, DateTime>> ordering = 
    s => s.dtUpdateDate;
if (viewType == HomepageViewType.MostViewed)
   ordering = (s) => s.vchUserField8;  // a DateTime
else if (viewType == HomepageViewType.MostEffective)
   ordering = (s) => s.vchUserField4;  // another  DateTime

Then this would work just fine (I tested it and it worked)

You could instead do a per type order by either a series of nested switch/if statements of by constructing a dictionary or similar structure to get them.

For the linq to sql to work without explicit dynamic creation of the query I believe it must know the precise type of the query as opposed to just it being an IComparable...

Answer 2

I think the unsupported part of your code is the use of IComparable as a general return type for your ordering expression. If you consider the plain use of OrderByDescending, the compiler-generated lambda expression has a return type of the type of the property that you're ordering by: for example, an Expression<Func<csExtendedQAIncident_doc, string>> for a string property.

One possible answer, although I'm not sure whether it works in your case, is to first create an unordered query:

IQueryable<Foo> unorderedQuery = from f in db.Foo select f;

And then, depending on the sort:

IOrderedQueryable<Foo> orderedQuery = unorderedQuery
    .OrderBy(f => f.DefaultSortKey);

if (sortBy == SortByName)
    orderedQuery = unorderedQuery.OrderBy(f => f.Name);
else if (sortBy == SortByDate)
    orderedQuery = unorderedQuery.OrderBy(f => f.Date);
// etc.