LINQ to SQL - Why can't you use a WHERE after an ORDER BY?

Question

The following code:

// select all orders
var orders = from o in FoodOrders
             where o.STATUS = 1
             order by o.ORDER_DATE descending
             select o;

// if customer id is specified, only select orders from specific customer
if (customerID!=null)
{
    orders = orders.Where(o => customerID.Equals(o.CUSTOMER_ID));
}

gives me the following error:

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Linq.IOrderedQueryable'. An explicit conversion exists (are you missing a cast?)

I fixed the error by doing the sorting at the end:

// select all orders
var orders = from o in FoodOrders
             where o.STATUS = 1
             select o;

// if customer id is specified, only select orders from specific customer
if (customerID!=null)
{
    orders = orders.Where(o => customerID.Equals(o.CUSTOMER_ID));
}

// I'm forced to do the ordering here
orders = orders.OrderBy(o => o.ORDER_DATE).Reverse();

But I'm wondering why is this limitation in place? What's the reason the API was designed in such a way that you can't add a where constraint after using an order by operator?

Answer 1

The return type of OrderBy is IOrderedQueryable<T>, so that's the type of the orders variable (partly because you have a no-op projection at the end) - but the return type of Where is just IQueryable<T>. Basically you've got a mixture of a no-op projection and an implicitly typed local variable and the last active part of the query is an ordering, and you're then wanting to reassign the variable. It's an unhappy combination, basically.

You could fix it like this:

IQuerable<FoodOrders> orders = from o in FoodOrders
                               where o.STATUS == 1
                               order by o.ORDER_DATE descending
                               select o;

// if customer id is specified, only select orders from specific customer
if (customerID!=null)
{
    orders = orders.Where(o => customerID.Equals(o.CUSTOMER_ID));
}

Alternatively, if you performed the no-op projection explicitly using dot notation (I suspect the SQL translator will be smart enough to cope!) the type inference would be okay:

var orders = FoodOrders.Where(o => o.STATUS == 1)
                       .OrderByDescending(o => o.ORDER_DATE)
                       .Select(o => o);

// if customer id is specified, only select orders from specific customer
if (customerID!=null)
{
    orders = orders.Where(o => customerID.Equals(o.CUSTOMER_ID));
}

Or as a final and slightly odd suggestion, you could just change the order of your initial where and orderby clauses. This would be a bad idea in LINQ to Objects, but shouldn't make a difference in LINQ to SQL:

var orders = from o in FoodOrders
             order by o.ORDER_DATE descending
             where o.STATUS == 1
             select o;

// if customer id is specified, only select orders from specific customer
if (customerID!=null)
{
    orders = orders.Where(o => customerID.Equals(o.CUSTOMER_ID));
}

Now, in terms of the "why" of the API design: OrderBy and OrderByDescending return IOrderedQueryable so that you can then chain it with ThenBy and ThenByDescending which rely on there being an existing ordering that they can become secondary to, if you see what I mean.