Inject TableName as Parameter for Update & Insert on GenericEntity in ServiceStack Ormlite

Question

I have 3 tables of same structure so i have created the following entity using ServiceStack

public class GenericEntity
{
    [Alias("COL_A")]
    public string ColumnA { get; set; }
}

For retriving the results I use the following line of code. In it I pass the table name like "TableA"/"TableB" so that i can pull the appropriate results

db.Select<GenericEntity>(w => w.Where(whereExperssion).OrderBy(o => o.ColumnA).From("TableA"));

For delete i use the following code

 db.Delete<GenericEntity>(w => w.Where(q => q.ColumnA == "A").From("TableA"));

With From() I can pass table name for SELECT & DELETE operations. Is there a similar way for Inserting and updating? The below is the snippet code I am using for update and insert

Insert

db.Insert(new GenericEntity() {});

Update

db.Update<GenericEntity>(new GenericEntity { ColumnA = "ModifiedData"},p => p.ColumnA == "OriginalData");

Cesarcesare

Answer 1

As you're wanting to this for multiple API's I've added a test showing how to achieve the desired behavior by extending OrmLite's API's with your own custom extension methods that modifies OrmLite's table metadata at runtime to add new API's that allow specifying the table name at runtime, i.e:

var tableName = "TableA"'
db.DropAndCreateTable<GenericEntity>(tableName);

db.Insert(tableName, new GenericEntity { Id = 1, ColumnA = "A" });

var rows = db.Select<GenericEntity>(tableName, q =>
    q.Where(x => x.ColumnA == "A"));

rows.PrintDump();

db.Update(tableName, new GenericEntity { ColumnA = "B" },
    where: q => q.ColumnA == "A");

rows = db.Select<GenericEntity>(tableName, q => 
    q.Where(x => x.ColumnA == "B"));

rows.PrintDump();

With these extension methods:

public static class GenericTableExtensions 
{
    static object ExecWithAlias<T>(string table, Func<object> fn)
    {
        var modelDef = typeof(T).GetModelMetadata();
        lock (modelDef) {
            var hold = modelDef.Alias;
            try {
                modelDef.Alias = table;
                return fn();
            }
            finally {
                modelDef.Alias = hold;
            }
        }
    }

    public static void DropAndCreateTable<T>(this IDbConnection db, string table) {
        ExecWithAlias<T>(table, () => { db.DropAndCreateTable<T>(); return null; });
    }

    public static long Insert<T>(this IDbConnection db, string table, T obj, bool selectIdentity = false) {
        return (long)ExecWithAlias<T>(table, () => db.Insert(obj, selectIdentity));
    }

    public static List<T> Select<T>(this IDbConnection db, string table, Func<SqlExpression<T>, SqlExpression<T>> expression) {
        return (List<T>)ExecWithAlias<T>(table, () => db.Select(expression));
    }

    public static int Update<T>(this IDbConnection db, string table, T item, Expression<Func<T, bool>> where) {
        return (int)ExecWithAlias<T>(table, () => db.Update(item, where));
    }
}

Adding your own extension methods in this way allows you to extend OrmLite with your own idiomatic API's given that OrmLite is itself just a suite of extension methods over ADO.NET's IDbConnection.

Photoengraving

I am not able to find GetModelMetadata() is there any namespace or class missing or did i miss anything – Cesarcesare

@Shil it's an extension method in the ServiceStack.OrmLite namespace. Update to the latest version of OrmLite if it still doesn't show up. – Photoengraving

I initially updated only the DAL layer ServiceStack so i was getting errror. Later came to know we need update all the layers which ever uses Servicestack. I made some few changes instead of Update() i used UpdateOnly() and code works perfectly well Thanks @mythz. – Cesarcesare