Is there a way to limit / page result sets

Question

Is there a way to limit / page result sets using GetAllWithChildren() or GetAll() or GetAllWithChildrenAsync() or GetAllAsync().

These methods accept a filtering expression but it doesn't appear that there is any straightforward way of setting an explicit limiter or orderby clause.

Answer 1

GetAllWithChildren method is just a convenience method. To perform custom queries you have to use the Table method. It shouldn't be difficult to implement your own fetch method with the functionality that you want extending the already existing GetAllWithChildren:

public static class OrderExtensions {
    public static List<T> GetAllWithChildren<T>(this SQLiteConnection conn,
            Expression<Func<T, bool>> filter = null,
            Expression<Func<T, object>> orderExpr = null,
            int? limit = null,
            int? offset = null,
            bool recursive = false) where T: class
    {
        var elements = conn.Table<T>();
        if (filter != null) {
            elements = elements.Where(filter);
        }
        if (orderExpr != null) {
            elements = elements.OrderBy(orderExpr);
        }
        if (offset != null) {
            elements = elements.Skip(offset.Value);
        }
        if (limit != null) {
            elements = elements.Take(limit.Value);
        }

        var list = elements.ToList();

        foreach (T element in list)
        {
            conn.GetChildren(element, recursive);
        }

        return list;
    }
}

Then you can use it like this:

    conn.GetAllWithChildren<MyClass>(
            filter: o => o.Name != "",
            orderBy: o => o.Name,
            limit: 10, offset: 20);

or the less verbose (and less descriptive) version:

    conn.GetAllWithChildren<MyClass>(o => o.Name != "", o => o.Name, 10, 20);