LINQ OrderBy().ThenBy() not working

Question

I am converting a project of mine from using an SQL Server based Entity Framework model, to using a local SQLite database. So far everything is going well, but for some reason I am not able to sort queries on multiple columns. For example:

using (var db = new SQLiteConnection("test3.db"))
{
    var query = from a in db.Table<Account>()
        where a.Inactive == false
        orderby a.AccountName, a.AccountNickname
        select a;
    foreach (var account in query)
    {
        accounts.Add(account);                    
    }
}
AccountsGrid.ItemsSource = accounts;

Gives me the error Cannot resolve symbol 'ThenBy', but if I change the ordering to:

orderby a.AccountName

then the query works fine. I have also tried using .OrderBy(a => a.AccountName).ThenBy(a => a.AccountNickname) but I get the same error. I am already including using System.Linq; but ReSharper is telling me that the using directive is not required, so that seems fishy also. Does anyone have any ideas what I could be missing?

Solution 1

Looking at the source, it seems the author doesn't understand LINQ properly:

• They haven't provided a ThenBy method, but instead they're collecting multiple orderings with multiple OrderBy calls
• They haven't exposed the table as an IQueryable<T> implementation (which is why you don't need using System.Linq) - while this is a valid approach, it's a pretty unusual one

I would personally be pretty nervous about using this - the fact that it's "organized" as three huge source files is slightly alarming too. You may want to try using LinqConnect instead - although I haven't used that, either.

If you do want to stick with the implementation you're using, I suspect that this would work - but it wouldn't work with other LINQ providers:

var query = from a in db.Table<Account>()
    where a.Inactive == false
    orderby a.AccountName
    orderby a.AccountNickname // Warning! Not portable!
    select a;

Normally having two orderby calls like this would be a really, really bad idea - but it seems that that's what the LINQ provider wants in this case.

Solution 2

    In Linq Query

var query = (from a in db.Table<Account>()
            where a.Inactive == false
            orderby a.AccountName ascending, a.AccountNickname descending
            select m);

      In Lambda Expression

var query = db.Table<Account>().where(a => a.Inactive == false).OrderBy(a => a.AccountName).ThenByDescending(a =>a.AccountNickname);

Comments

[dub stylee]

I was previously using the System.Data.SQLite nuget package by the SQLite development team, but it didn't seem to be as flexible. When I came across the sqlite-net project, it seemed to be more user friendly, but maybe I didn't give the official one a good enough chance. I will look into LinqConnect as well! Thanks.

[dub stylee]

is there a way to view the methods available in a package? I am trying to determine if what I'm trying to achieve is feasible using the System.Data.SQLite package rather than the sqlite-net package I am currently using. When I was using System.Data.SQLite before, it was a lot more tedious to accomplish anything, but I may have just not known its capabilities.

[Jon Skeet]

@dubstylee: Well you'd need to try to find the documentation, basically. Or you can just use Intellisense to explore the API, of course.

[dub stylee]

I just wanted to let you know, I gave LinqConnect a shot today, and it is leaps and bounds better than the sqlite-net that I was using before. It supports full LINQ-like behavior, as far as I can tell. Thanks the the tip!

[dub stylee]

This does not answer the question at all, you are merely showing two equivalent expressions.