CODEWITHSUNDEEP
sqlitexamarin
jool
jool

Reputation: 73

SQL Lite Xamarin : Query

I'm newbie in SQLite.

I would like to query my SQLite database to get multiple rows.

When I add a new item in my local database I call this method Add:

public bool Add<T>(string key, T value)
{
    return this.Insert(new SQliteCacheTable(key, this.GetBytes(value))) == 1;
}

_simpleCache.Add("favorite_1", data1);
_simpleCache.Add("favorite_2", data2);
_simpleCache.Add("favorite_3", data2);

Then,

I would like to retrieve from local database all entries where key starts with "favorite_" to returns all objects in the database which are "favorite" objects.

I'm experienced in Linq, and I would like to do something like this:

IEnumerable<Element> = repository.Find((element) => element.Key.StartWith("favorite_"))

In the SQLiteConnection class there is a method like this:

SQLite.Net.SQLiteConnection.Find<T>(System.Linq.Expressions.Expression<System.Func<T,bool>>)

But I would like the same with in returns a collection IEnumerable<T>.

Can you help me please?

Thank you.

Jool

Upvotes: 1

Views: 906

Answers (1)

madd0
madd0

Reputation: 9323

You have to build your query on the table itself, not the connection:

Assuming:

SQLiteConnection repository;

Then the code would look like:

var favorites = repository.Table<SQliteCacheTable>().Where(item => item.StartsWith("favorite_"));

The favorites variable is of type TableQuery<SQliteCacheTable> though, so it does not yet contain your data. The execution of the actual SQL query will be deferred until you try to access the results (by enumerating with foreach or converting to a list with ToList, for example).

To actually observe what's going on on the database, you can turn on tracing in sqlite-net, by setting repository.Trace = true on your SQLiteConnection object.

Finally, it's worth mentioning that you can also use the C# query syntax on TableQuery<T> objects, if your comfortable with it. So your query could become:

var favorites = from item in repository.Table<SQliteCacheTable>()
                where item.StartsWith("favorite_")
                select item;

Upvotes: 3

Related Questions