Is it possible to batch queries or load a hierachy in one request

ServiceStack.OrmLite
1

If I have some structure of objects:

  • class A which have its fields and a reference to class B
  • class B which have its fields and a reference to class C
  • class C with some properties

Is it possible to populate this full chain with one single request to the database in some way? And by that, I mean:

  • Does Select(…, references: true) execute this hierarchy as one sql query?
  • Or can I do something to tell ServiceStack to batch the queries in one round like:
using(var batch = Db.BatchQueries())
{
  a = batch.SelectById(1)
  a.b = batch.SelectById(2)
  a.b.c = batch.SelectById(3)
}

And when the using clause exits, the queries are sent as three select statements but in the same batch?

2

If by “batch” you mean transaction, you can easily make the calls within a transaction.

However, if you mean “batch” as in multiple result sets coming back from a single query, then no. See @mythz comment at http://stackoverflow.com/questions/23144058/servicestack-ormlite-get-multiple-result-sets-from-a-stored-procedure. It’s not built into OrmLite, however you can use the embedded Dapper’s QueryMultiple to achieve this. Something along the lines of…

var sql = @"select * from Customer where Id = @id
            select * from ProfileCode where CustomerId = @id";
using (var db = _dbFactory.OpenDbConnection())
using (var multi = db.QueryMultiple(sql, new { id = 2 }))
{
    var customer = multi.Read<Customer>().Single();
    customer.ProfileCodes = multi.Read<ProfileCode>().ToList();
    Console.WriteLine("Customer: " + customer.Dump());
}

Of course, you’ll need to using ServiceStack.OrmLite.Dapper; to get to QueryMultiple.

1 Like