How can I JOIN or Attach multiple SQLite DBs using ServiceStack OrmLite?

Question

The excellent ServiceStack OrmLite has a ton of features.

I have a scenario where I have two or more separate SQLite DBs, how can I join/attach them using OrmLite?

Do I have to write RAW SQL or are there any built-in features I can use?

Thank you

Answer 1

OrmLite supports Multi-nested named connections which lets you register multiple RDBMS connections under different names, e.g:

//Set default connection
var dbFactory = new OrmLiteConnectionFactory(
    "~/App_Data/db.sqlite".MapAbsolutePath(), SqliteDialect.Provider);

//Setup multiple named connections
dbFactory.RegisterConnection("db1", 
    "~/App_Data/db1.sqlite".MapAbsolutePath(), SqliteDialect.Provider);

dbFactory.RegisterConnection("db2", 
    "~/App_Data/db2.sqlite".MapAbsolutePath(), SqliteDialect.Provider);

You can then access each Sqlite db with the registered name.

As Sqlite doesn't support cross-database joins, you can't create a SQL query that spans multiple DB's, so if you want to merge results from both you'd need to do it in code, e.g:

var results = new List<Table>();
using (var db1 = dbFactory.OpenDbConnection("db1"))
using (var db2 = dbFactory.OpenDbConnection("db2"))
{
    results.AddRange(db1.Select<Table>(q => q.Category = category));
    results.AddRange(db2.Select<Table>(q => q.Category = category));
}