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

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?

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 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