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
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:
I went around that using basically the code you use in the library…
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:
insertField is now being passed in this commit (also on MyGet).
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.
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?
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);
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.
Wow thank you! I tried your new implementation, but I run in an error:
INSERT INTO "LRARISULTATI" ("AANALISIID",
SELECT DISTINCT "LRAANALISI"."IDAANALISI" AS "AnalisiId",
1 AS TipoValore,
N'' AS OperatoreRelazionale,
1 AS TestoCodificatoId,
"LRDRISULTATI"."IDDRISULTATO" AS "RisultatoId",
0 AS Stato,
0 AS TipoInserimento,
N'20181129 09:49:54.5600742' AS DataOraRicezione,
N'20181129 09:49:54.5600742' AS DataModifica,
1 AS VersioneRecord
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.
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.
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.