Insert into... select

sviluppo1 · November 27, 2018, 9:47am

Hi mythz, is it possible to create a INSERT INTO SELECT in OrmLite?

I have a SqlExpression and I just want to insert all those record in a table, something like this:

var q = DB.From<SourceTable>()
        .Select(t => new 
        {
            Param = 1,
            Descr = t.Descr
        });

long result = DB.InsertIntoSelect<DestinationTable>(q);

And I expect the following output:

INSERT INTO DestinationTable (Param, Descr)
SELECT 1 as Param, t.Descr
FROM SourceTable

I saw there is a overload of Insert that allows a commandFilter but I don’t think it does what I’m asking. Thanks.

EDIT: if it’s not possible, how can I generate the full SQL statement, filters included, from a SqlExpression? So I can build a literal command using the SELECT statement generated by OrmLite. I saw that INSERT INTO SELECT syntax is pretty much the same across many dbms (mssql, postgresql, sqlite). I’m stuck trying to get the field names out from the Select Statement.

Also i’m trying to use ToInsertStatement to generate the statement automatically, but insertField is ignored:

github.com

ServiceStack/ServiceStack.OrmLite/blob/1ef91856b87fe9127873b06c73b38fcbd0baf8d9/src/ServiceStack.OrmLite/OrmLiteDialectProviderBase.cs#L601


    var sql = $"INSERT INTO {GetQuotedTableName(modelDef)} ({StringBuilderCache.ReturnAndFree(sbColumnNames)}) " +

              $"VALUES ({StringBuilderCacheAlt.ReturnAndFree(sbColumnValues)})";



    return sql;

}



public virtual string ToInsertStatement<T>(IDbCommand dbCmd, T item, ICollection<string> insertFields = null)

{

    dbCmd.Parameters.Clear();

    var dialectProvider = dbCmd.GetDialectProvider();

    dialectProvider.PrepareParameterizedInsertStatement<T>(dbCmd);



    if (string.IsNullOrEmpty(dbCmd.CommandText))

        return null;



    dialectProvider.SetParameterValues<T>(dbCmd, item);



    return MergeParamsIntoSql(dbCmd.CommandText, ToArray(dbCmd.Parameters));

}



protected virtual object GetInsertDefaultValue(FieldDefinition fieldDef)

I went around that using basically the code you use in the library…

mythz · November 27, 2018, 7:01pm

You can add a feature request on UserVoice but it’s only going to be able to support a small subset of SqlExpression where the Select List needs to match exactly with the column names of the table you want to Insert.

You can generate a Select statement from an SqlExpression with:

var sql = q.SelectInto<T>();

As most arguments are parameterized the SQL needs to be executed including the parameters in q.Params.

Due to the flexibility of SqlExpression it’s not available as a list of field names, you can get the select expression from q.SelectExpression.

Some functionality that may be helpful although not related to OrmLite (but it’s used to parse SELECT expressions in AutoQuery) is that you can parse a SELECT expression using the ParseCommands() extension method:

var fields = q.SelectExpression.Substring("SELECT ".Length).ParseCommands();

It tokenizes the expression making it easy to access field names, aliases and function arguments if used. Some tests with examples is available at:

github.com

ServiceStack/ServiceStack/blob/afff7d7246fa8ddf2e0a1e517b427e45b454d2af/tests/ServiceStack.Common.Tests/StringExtensionTests.cs#L168


      
              Assert.That("  http://host.example.com".ToHttps(), Is.EqualTo("https://host.example.com"));
          }
          
          [Test]
          public void ToHttps_is_not_case_sensitive()
          {
              Assert.That("HTTP://HOST.EXAMPLE.COM".ToHttps(), Is.EqualTo("https://HOST.EXAMPLE.COM"));
          }
          
          [Test]
          public void Can_parse_commands()
          {
              Assert.That("COUNT".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:]" }));
              Assert.That("COUNT()".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:]" }));
              Assert.That("COUNT(*)".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:*]" }));
              Assert.That("COUNT(DISTINCT Name)".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:DISTINCT Name]" }));
              Assert.That("COUNT('Name')".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:'Name']" }));
              Assert.That("COUNT(\"Name\")".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:\"Name\"]" }));
              Assert.That("COUNT('N,a(m\"e')".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:'N,a(m\"e']" }));
              Assert.That("COUNT(*,'foo',1)".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:*|'foo'|1]" }));
              Assert.That("COUNT( * , 'foo' , 1 )".ParseCommands().Map(x => x.ToDebugString()), Is.EquivalentTo(new[] { "[COUNT:*|'foo'|1]" }));

insertField is now being passed in this commit (also on MyGet).

sviluppo1 · November 28, 2018, 10:34am

So there is no way to get the list of field of the anonymous object? I see in fact you “discard” the expression after parse. Right now, I managed to let it work with something like this:

public static int InsertIntoSelect<TEntity>(this IDbConnection db, ISqlExpression expression) where TEntity : IHasId<int>
{
    // 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);
    string fullInsertStatement = db.ToInsertStatement(Activator.CreateInstance<TEntity>(),
        new[]
        {
            nameof(LRARisultato.AnalisiId),
            nameof(LRARisultato.TipoValore),
            nameof(LRARisultato.OperatoreRelazionale),
            nameof(LRARisultato.TestoCodificatoId),
            nameof(LRARisultato.RisultatoId),
            nameof(LRARisultato.Stato),
            nameof(LRARisultato.InviareALIS),
            nameof(LRARisultato.RisultatoPrincipale),
            nameof(LRARisultato.TipoInserimento),
            nameof(LRARisultato.DataOraRicezione),
            nameof(LRARisultato.DataModifica),
            nameof(LRARisultato.VersioneRecord),
        });
    string insertIntoSelectStatement = Regex.Replace(fullInsertStatement, "VALUES .*$", selectStatement);

    int risultatiInseriti = db.ExecuteSql(insertIntoSelectStatement);

    return risultatiInseriti;
}

The fixed array where you see all the nameofs should contain the same list of fields of the SELECT query.

EDIT: anyway, it would be nice if you could take my solution, and with the support of your internal APIs, write a more solid implementation.

mythz · November 28, 2018, 1:07pm

You can extract a list of field names from an anonymous expression using the ExpressionUtils.GetFieldNames() helper, e.g:

var fieldNames = ExpressionUtils.GetFieldNames((Poco x) => new { x.Id, x.Name });

It’s a versatile method that’s able to extract a list of field names from a variety of different inputs as seen in theses tests.

sviluppo1 · November 28, 2018, 1:15pm

And ok, that’s fine, but I was asking about extracting the “select list fields” from a SqlExpression. Because otherwise I have to specify the expression twice, once for the Select, and once for ToInsertStatement.

mythz · November 28, 2018, 1:31pm

Pass the select expression once in your InsertIntoSelect method, then pass it through to your SqlExpression.

sviluppo1 · November 28, 2018, 1:39pm

Yeah that should do, a bit strange to use tho. Right now, InsertIntoSelect accept the SqlExpression directly.

Anyway, I’m there but I can see that ToInsertStatement doesn’t keep the order of the fields I pass to that. They get messed around and my INSERT fails because the INSERT list and SELECT list are not synced anymore. I guess that ToInsertStatement produces fields in the same order that they appear in the TEntity item definition, am i right?

mythz · November 28, 2018, 1:45pm

Kind of, that’s the current behavior in .NET but there’s explicitly no guarantee in the order returned in .NET Reflection APIs (so could change in future), when needing exact ordering fetch it from the ModelDefinition, e.g:

var modelDef = typeof(MyType).GetModelMetadata();
var orderedFieldNames = modelDef.FieldDefinitionsArray
    .Where(x => !x.ShouldSkipInsert())
    .Map(x => x.FieldName);

sviluppo1 · November 28, 2018, 2:00pm

Ok so, since ToInsertStatement enforces a custom order, I should re-order the select statement fields based on that order?

That could be a problem… since I’m using aliases, I call ToInsertStatement with fieldNames equals to .net property names, but the output (correctly) contains Aliases, so it’s a bit complicated to reorder my select list based on the order produced by ToInsertStatement. Also because GetModelMetadata gives me back the model of the From<> but if I have multiple tables i’m screwed.

sviluppo1 · November 28, 2018, 3:45pm

Ok, I’ve managed to let it work like this:

gist.github.com

https://gist.github.com/samusaran/992cff077ac765cd1f81cdc9411acc1e

InsertIntoSelect.cs

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();

This file has been truncated. show original

mythz · November 28, 2018, 6:21pm

That’s an option, but IMO it’s better to leave the SELECT expression untouched and rearrange the Insert fields to adopt the order of insertFields if any were provided.

In order to be able to do this I updated OrmLite to adopt the ordering of insertFields in this commit.

I’ve also added a built-in InsertIntoSelect<T> API adopting this approach in this commit which leads to a simpler impl.

The new built-in InsertIntoSelect<T> API is available from v5.4.1 that’s now available on MyGet, if you already have v5.4.1
installed you’ll need to clear your NuGet cache.

sviluppo1 · November 29, 2018, 9:50am

Wow thank you! I tried your new implementation, but I run in an error:

Resulting INSERT:

INSERT INTO "LRARISULTATI" ("AANALISIID",
                            "TIPOVALORE",
                            "OPERATORERELAZIONALE",
                            "DTESTOCODIFICATOID",
                            "DRISULTATOID",
                            "STATO",
                            "TIPOINSERIMENTO",
                            "DATAORARICEZIONE",
                            "DATAMODIFICA",
                            "VERSIONERECORD")
SELECT DISTINCT "LRAANALISI"."IDAANALISI" AS "AnalisiId",
                1 AS TipoValore,
                N'' AS OperatoreRelazionale,
                1 AS TestoCodificatoId,
                "LRDRISULTATI"."IDDRISULTATO" AS "RisultatoId",
                0 AS Stato,
                "LRDRISULTATI"."INVIAREALIS", --missing?
                "LRDRISULTATI"."RISULTATOPRINCIPALE", --missing?
                0 AS TipoInserimento,
                N'20181129 09:49:54.5600742' AS DataOraRicezione,
                N'20181129 09:49:54.5600742' AS DataModifica,
                1 AS VersioneRecord
FROM "LRACONTENITORI"
INNER JOIN "LRARICHIESTE" ON ("LRACONTENITORI"."ARICHIESTAID" = "LRARICHIESTE"."IDARICHIESTA")
INNER JOIN "LRAANALISI" ON ("LRACONTENITORI"."IDACONTENITORE" = "LRAANALISI"."ACONTENITOREID")
INNER JOIN "LRDANALISI" ON ("LRAANALISI"."DANALISIID" = "LRDANALISI"."IDDANALISI")
INNER JOIN "LRDRISULTATI" ON ("LRDANALISI"."IDDANALISI" = "LRDRISULTATI"."DANALISIID")
LEFT JOIN "LRARISULTATI" ON (("LRAANALISI"."IDAANALISI" = "LRARISULTATI"."AANALISIID")
                             AND ("LRDRISULTATI"."IDDRISULTATO" = "LRARISULTATI"."DRISULTATOID"))
WHERE ("LRARICHIESTE"."ARCHIVIATA" = @0)
  AND ("LRACONTENITORI"."DATAORAPRIMOCHECKIN" IS NOT NULL)
  AND ("LRACONTENITORI"."DATAORAESECUZIONE" IS NOT NULL)
  AND ("LRARISULTATI"."IDARISULTATO" IS NULL)
  AND ("LRARICHIESTE"."DATAORAACCETTAZIONE" < @1)

Two field from the select list are missing from the Insert Fields.

Also, I was looking at your tests. I think a function like this should return the number of rows affected instead of the ID, since 99% of the time an INSERT like this will produce more than 1 record.
Thanks!

mythz · November 29, 2018, 11:20am

Please provide a stand-alone example I can run to repro the issue.

It was already returning the number of rows inserted, the test just used wrong variable name that’s now resolved.

sviluppo1 · November 30, 2018, 10:25am

Right now the ordering of insertFields works inconsistently across DialectProviders. Right now it works fine with SqliteDialect but not with SqlServer2016Dialect. It works with SqlServerDialect tho.

mythz · November 30, 2018, 12:13pm

I’ve tested the InsertIntoSelect test which works with all RDBMS providers including SqlServer2016, please provide a stand-alone example that doesn’t.

sviluppo1 · November 30, 2018, 1:09pm

github.com

ServiceStack/ServiceStack.OrmLite/blob/44a52e113cf4369d501250b6c5ce643749c48e6d/tests/ServiceStack.OrmLite.Tests/OrmLiteInsertTests.cs#L405


    Salt = "Salt",
    PasswordHash = "PasswordHash",
    CreatedDate = DateTime.Now,
    ModifiedDate = DateTime.UtcNow,
};
db.Insert(userAuth);


var q = db.From<UserAuth>()
    .Where(x => x.UserName == "UserName")
    .Select(x => new {
        x.UserName, 
        x.Email, 
        GivenName = x.FirstName, 
        Surname = x.LastName, 
        FullName = x.FirstName + " " + x.LastName
    });


var rowsInserted = db.InsertIntoSelect<SubUserAuth>(q);
Assert.That(rowsInserted, Is.EqualTo(1));


var result = db.Select<SubUserAuth>()[0];

Use yours. Change the order of the fields, trying to not respect the order of the properties in the SubUserAuth poco. The only reason your test is working is because your anonymous object has the fields in the same order they are defined into SubUserAuth.

mythz · November 30, 2018, 2:14pm

ok that was primarily an issue with parsing commands that’s now resolved in latest v5.4.1 on MyGet.

FYI if you’re using .ParseCommands() in your own code you’ll likely want to switch to use command.Original as that now contains the original string for each selected field whereas Name would only contain the name of the Function if it starts with one.

sviluppo1 · December 3, 2018, 9:49am

Here’s the repo with the issue I currently have. It’s seems to be because of the DateTime2 converter.

Let me know, thanks a lot!

mythz · December 3, 2018, 10:15am

This runs without issue using the latest OrmLite:

The only thing I’ve changed is the connectionString to use my local SQL Server instance.

sviluppo1 · December 3, 2018, 10:28am

Ok my OrmLite.SqlServer was still at 5.4.0. Only OrmLite base package was at 5.4.1.

Thanks for the help!