Arbitrary/string return value from LiteDb query

Question

I have a LiteDb database on which I would like the user to be able to run arbitrary queries (given as a string; assume that we demand their query also always returns a string). Unfortunately I seem to be locked into always receiving the type given in the collection. For example the query

SELECT "hello"

is valid SQL as far as I understand but LiteDb doesn't parse it:

new LiteDatabase(dbFile).GetCollection<MyType>().Find("SELECT 'hello'");

Gives the error

Unexpected token HELLO in position 10.

Of course it's not unexpected that this example will fail, since Find is supposed to always return an IEnumerable<MyType> but I can't find a way that can run an arbitrary valid query. Is there a good way around this? I understand the LiteDb is not supposed to be an SQL server but I need the kind of flexibility to allow the user to get strings back from their logic; sort of like allowing them to program the database a bit.

Answer 1

You can use the Execute() method in your LiteDatabase instance. It basically returns you a collection of BsonValues:

var result = liteDatabase.Execute("SELECT 'John' AS Name, 34 AS Age").ToEnumerable()
    .SelectMany(x => ((Dictionary<string, BsonValue>)x.RawValue)
    .ToDictionary(y => y.Key, y => y.Value.RawValue.ToString()));

foreach (var r in result) Console.WriteLine($"{r.Key}: {r.Value}");

However, this comes with a great burden that puts you in the obligation to consider all possible forms of complexity. In this simple case you will get a KeyValuePair, but with more complex queries you have to deal with a more complex output - so I recommend you to limit yourself on just handling KeyValuePairs or comparable data structures.