How to Order By Descending with SQLite.NET? OrderByDescending() gives me error: "Order By does not support: xx => Convert(xx).ID"

Question

I am trying to implement a method to retrieve all the rows in a table, ordered by ID descending.

This is the code:

    public IEnumerable<T> GetItemsDescending<T>() where T : IBusinessEntity, new()
    {
        lock (locker) 
        {
            return Table<T>().Select(i => i).OrderByDescending(xx => xx.ID).ToList();
        }
    }

This looks like it should work, but I'm getting an error I don't understand:

        "Order By does not support: xx => Convert(xx).ID"

from the following SQLite method:

private TableQuery<T> AddOrderBy<U>(Expression<Func<T, U>> orderExpr, bool asc)
{
    if (orderExpr.NodeType == ExpressionType.Lambda)
    {
        var lambda = (LambdaExpression)orderExpr;

        MemberExpression mem = null;

        var unary = lambda.Body as UnaryExpression;
        if (unary != null && unary.NodeType == ExpressionType.Convert)
        {
            mem = unary.Operand as MemberExpression;
        }
        else
        {
            mem = lambda.Body as MemberExpression;
        }

        if (mem != null && (mem.Expression.NodeType == ExpressionType.Parameter))
        {
            var q = Clone<T>();
            if (q._orderBys == null)
            {
                q._orderBys = new List<Ordering>();
            }
            q._orderBys.Add(new Ordering
            {
                ColumnName = Table.FindColumnWithPropertyName(mem.Member.Name).Name,
                Ascending = asc
            });
            return q;
        }
        else
        {
            throw new NotSupportedException("Order By does not support: " + orderExpr);
        }
    }
    else
    {
        throw new NotSupportedException("Must be a predicate");
    }
}

as requested: The PanelLog class:

public class PanelLog : IBusinessEntity
{
    public PanelLog()
    {

    }

    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public uint Sequence { get; set; }
    public DateTime Time { get; set; }
    public string Message { get; set; }
    public bool Alarm { get; set; }
}

score 5 · Accepted Answer · answered Jun 14 '13 at 13:24

5

No answers?

Ok, well this is what I'm doing as a work around:

public static IEnumerable<PanelLog> GetPanelLogsDescendingSql(params object[] args)
{
    return me.db.Query<PanelLog>("select * from PanelLog ORDER BY ID DESC");        
}

answered Jun 14 '13 at 13:24

DaveDev

41,155
72
223
385

What is the field type of ID? Can you post your PanelLog class ? – Greg Munn Jun 17 '13 at 05:57
Sorry, given the code you've supplied, I can't replicate the issue your reporting. Do you happen to have a full sample that replicates this issue? – Greg Munn Jun 17 '13 at 11:38
3

The TaskyPro sample, available here: https://github.com/xamarin/mobile-samples/tree/master/TaskyPro is fairly similar in structure to what I'm doing. If you change the line `return (from i in Table () select i).ToList ();` to `return (Table().OrderByDescending(i => i.ID).Select(i => i)).ToList();` in the `TaskDatabase` class it causes the same error. – DaveDev Jun 17 '13 at 11:52
Sorry about the delay, I've downloaded TaskyPro and made the change (in TaskDatabase.GetItems) and it works for me. I'm running the alpha at the moment - iOS v6.3.6.76. – Greg Munn Jun 19 '13 at 00:37
(ran fine in simulator and device and the Android emulator) – Greg Munn Jun 19 '13 at 00:45
Ok thanks - I'll look deeper into my own setup so. It appears to be a problem on my side. – DaveDev Jun 19 '13 at 08:15
In case you consider interesting you can make it generic with public IEnumerable GetItemsOrderedBy(Expression> expression) ...return database.Table().OrderBy(expression).Select(i => i); thanks @DaveDev – Juan Pablo Garcia Coello Nov 11 '16 at 19:22

score 0 · Answer 2 · edited Mar 30 '21 at 18:45

0

return Table<T>().Select(i => i).OrderByDescending(xx => xx.ID).ToList();

or

return me.db.Query<PanelLog>("select * from PanelLog ORDER BY ID DESC");

Works for me.

edited Mar 30 '21 at 18:45

Paul Roub

36,322
27
84
93

answered Mar 30 '21 at 18:25

JesusTorres

1

How to Order By Descending with SQLite.NET? OrderByDescending() gives me error: "Order By does not support: xx => Convert(xx).ID"

2 Answers2