Litedb: how can i order in db

[JsonSong89]

Hi : Mauricio
thanks for your project first .
i have a doubt that why there is no order in Find

IEnumerable<T> Find(Query query, int skip = 0, int limit = int.MaxValue)

for example , i want take latest 100 docs , i have a field "AddedTime"
the normal way is order by AddedTime desc then skip 0 and take(limit) 100 ,that's what i needed .
but without order ,the take is no sence .

what should i do ?

[mbdavid]

Hi @178220709,

If you want take your lasted 100 docs using indexes, you can:

1) Create an index on AddedTime
2) Run `collection.Find(Query.All("AddedTime", Query.Descending), 0, 100);

Now you will list all yor documents in AddedTime desc order and get only 100 first.

[JsonSong89]

Hi: Mauricio
thanks for you reply.

this is a little strange that order and query together
and there is a overload

IEnumerable<T> Find(Expression<Func<T, bool>> predicate, int skip = 0, int limit = int.MaxValue)

can i do my above example by this function?
looks like if i need order in db, i can't use linq ?

how about this function

IEnumerable<TEntity> Find(Expression<Func<TEntity, bool>> predicate, Dictionary<string,int> orderKeys, int skip = 0, int limit = int.MaxValue)

and we use

var orderKeys = new Dictionary<string, int>()
            {
                {"AddedTime", Query.Descending},
                {"OtherFiled", Query.Ascending}
            };

to control the order

[mbdavid]

Hi @178220709,

Thanks for your idea, but LiteDB doesn´t have order operations (like a relation database). LiteDB has only indexed fields that can be used to Find results in ASC or DESC order (use a skip-list index structure). The only way to get a sorted data (using an index) is when you get operation like All, `>´, ´<´ so I start searching data from begin to end (ASC) or end to begin (DESC). But it´s not the same of sort your document results.

And why LiteDB has no internal order operations? Because .NET has implemented this operations in linq-to-object. Find method returns an IEnumerable<T> so you can run any local (non-indexed) linq operation in results only (like Order/GroupBy/Distinct).

col.Find(x => x.FirstName == "John").OrderBy(x => x.LastName)

[JsonSong89]

oh!
i guess i know the means of LiteDB.
because this is the local db , the cost of data transport is less than other service dbms .
after the filter , as much as possible,we should use linq to object in memory?
even ,we use Query.All immediaty , and do every operation in memory(when data is not very large)?

[mbdavid]

You right, local db has no network and all operations must be executed in local machine.

The only optimization that can be do it is filter. So, Find operation use indexed document field. This is valid optimation. When you use Query.All and than implement a Where linq, it´s more expensive because you need read all data pages and deserialize all documento only to know if match with your clause.

So, my recomendation is: always use Find (when possible) and then use normal Linq implementation.