Cannot convert IQueryable<> to IOrderedQueryable error

Question

I have the following LINQ code:

    var posts = (from p in db.Posts
         .Include("Site")
         .Include("PostStatus")
        where p.Public == false
        orderby p.PublicationTime 
        select p);

        if (!chkShowIgnored.Checked) {
            posts = posts.Where(p => p.PostStatus.Id != 90);
        }

That last line (the extra where) is giving me the error:

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Linq.IOrderedQueryable'.

I'm not sure what this means...
Why am I getting this error?
It appeared once I added the "orderby" clause to the query, before that it compiled fine, so I have kind of a hunch of what is going on, but I can't quite put my finger into it.

Rhinarium

Answer 1

Try declaring posts specifically as IQueryable<Post> rather than var (which will pick up the IOrderedQueryable<Post> (it will still be ordered).

Alternatively, re-structure it so we order at the end, allowing us to (optionally) bring in the where in the middle:

var posts = from p in db.Posts
             .Include("Site")
             .Include("PostStatus")
            where p.Public == false
            select p);

if (!chkShowIgnored.Checked) {
    posts = posts.Where(p => p.PostStatus.Id != 90);
}
var finalQuery = posts.OrderBy(p => p.PublicationTime);

(obviously, we look at finalQuery)

The reason it is erroring is that currently you have (essentially):

IOrderedQueryable<Post> posts = {snip};
...
posts = {something (Where) that returns IQueryable<Post>}

Misapply

Both worked like a charm, thank you! What do you mean we order at the end rather than in the middle? Isn't the actual SQL generated and executed after all this, once the query is enumerated? I assumed LINQ would be smart enough to "combine" the WHEREs... – Rhinarium

It is the OrderBy that changes the signature... by making the OrderBy the last thing we apply, we have IQueryable<T> throughout, which is easier to compose. As you say, the provider (EF, LINQ-to-SQL etc) will merge everything before executing it anyway. – Misapply

Aaaaah, that final explanation is SUPER clear, gotcha. Thank you!! – Rhinarium

Answer 2

The result of the lambda expressionis of type IQueryable. It doesn't allows the extension method Where, so to use it first you must convert this to, for example, a list.

You could do this using

posts = posts.ToList().Where(p => p.PostStatus.Id != 90);

Danyel

Hmmmmm, but wouldn't that first execute a query that'll bring all records from the database, and then filter them by status in ASP.Net? I'd rather have the DB process all the WHEREs, which is what I thought happened if I just added the .Where clause, since the SQL would be generated and executed later, when the results is enumerated. In your case, doesn't ToList() cause it to be executed too? – Rhinarium