user1 user1 - 1 year ago 103
C# Question

Is it possible to return dynamic objects or Dataset from a Sqlite Query?

I am using Sqlite.Net in my

application. So far it has been great at returning lists of objects if my object is a class like so:

SqliteDatabase.Connection.Query<Customer>("Select * from Customers");

I would now like to return the equivalent of a
dynamically from my query

SqliteDatabase.Connection.Query("Select * from Customers inner join Calls on Customers.Id=Calls.CustomerId")

Now from the second query I would like to return a
instead of a list of objects. I know I could create a new object which combines the columns of
but I don't want to have to create objects every time I want to query the database.

Is it possible to just dynamically return a

Answer Source

SQLite.NET PCL is a .NET wrapper around sqlite.

Therefore you can query similar to EF by using a join in in LINQ or Lambda than in the Query. The wrapper will handle the conversion to sqlite query for you.

You can then return a new datatype of the joined type or a dynamic type.

Note : Joins are not directly supported in sqlite (more info) and work around is mentioned here.

Sample Code :

var conn = new SQLiteConnection(sqlitePlatform, "foofoo");
var query = from customer in conn.Table<Customers>().ToList()
            join call in conn.Table<Calls>().ToList()
                         on customer.ID equals call.CustomerId                
            select new { Customer = customer , Calls = call };

Lambda version:

customer => customer.Id,
call => call.CustomerId, 
(customer, call) => new { Customer = customer, Calls = call });