Instantly share code, notes, and snippets.

Embed
What would you like to do?
ServiceStack.OrmLite extension for INSERT INTO ... SELECT
public static int InsertIntoSelect<TEntity>(this IDbConnection db, ISqlExpression expression) where TEntity : IHasId<int>
{
var modelDef = typeof(TEntity).GetModelMetadata();
var untypedExpression = expression.GetUntypedSqlExpression();
// Tokenize the select list
var commands = untypedExpression
.SelectExpression
.Substring("SELECT ".Length)
.ParseCommands();
// Get field names from the tokenized list
var fields = commands
.ToDictionary(k => Regex.Match(k.Name, @"(?>.+ AS )?(?>""?\w+""?.)?""?(\w+)""?").Groups[1].Value, v => v);
// ToInsertStatement reorders all fields based on Model Metadata order.
string fullInsertStatement = db.ToInsertStatement(Activator.CreateInstance<TEntity>(), fields.Keys);
// https://github.com/ServiceStack/ServiceStack.OrmLite/blob/67d57acbc65ab200219b12f5ed7a0ecb8ac19b69/src/ServiceStack.OrmLite/OrmLiteDialectProviderBase.cs#L628
var reorderedSelectList = new List<string>();
foreach (var fieldDef in modelDef.FieldDefinitionsArray)
{
if (fieldDef.ShouldSkipInsert())
continue;
if (fields.ContainsKey(fieldDef.Name))
{
reorderedSelectList.Add(fields[fieldDef.Name].Name);
continue;
}
if (fields.ContainsKey(fieldDef.FieldName))
reorderedSelectList.Add(fields[fieldDef.FieldName].Name);
}
// rebuild the select expression using the re-ordered select list
untypedExpression.SelectExpression = reorderedSelectList
.Aggregate(new StringBuilder(),
(sb, f) => sb.AppendFormat("{0},", f),
sb =>
{
sb.Length--; // skip trailing comma
return $"SELECT {sb.ToString()}";
});
// https://github.com/ServiceStack/ServiceStack.OrmLite/blob/67d57acbc65ab200219b12f5ed7a0ecb8ac19b69/src/ServiceStack.OrmLite/Expressions/SqlExpression.cs#L1279
var sql = expression.ToSelectStatement();
string selectStatement = db.GetDialectProvider().MergeParamsIntoSql(sql, expression.Params);
// replace VALUES with the user query
string insertIntoSelectStatement = Regex.Replace(fullInsertStatement, "VALUES .*$", selectStatement);
//
int rowsAffected = db.ExecuteSql(insertIntoSelectStatement);
return rowsAffected;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment