search

JordanMarr / SqlHydra

SqlHydra is a suite of NuGet packages for working with databases in F# including code generation tools and query expressions.
MIT License
223 stars 22 forks source link

Support for insert many? #5

Closed MargaretKrutikova closed 2 years ago

MargaretKrutikova commented 2 years ago

I can't seem to find a way to do insert of multiple entities in one query, it does seem to be supported by sqlkata. Is there a way to achieve this with insert-computation expression?

JordanMarr commented 2 years ago

If this is a feature of SqlKata then it definitely could (and should) be added to SqlHydra! Perhaps an entities operation could be added to the insert builder.

MargaretKrutikova commented 2 years ago

There is this overload of AsInsert:

public Query AsInsert(
      IEnumerable<string> columns,
      IEnumerable<IEnumerable<object>> rowsValues)

that I think can be used for insert-many. Would be also nice to not have to pass a list of columns but automatically infer them as it is currently done for a single entity.

JordanMarr commented 2 years ago

That overload doesn't take returnId as in input. Hopefully the current overload that takes KeyValuePair seq will work for multiple columns.

Does SqlKata allow returning multiple ids for multiple inserts?

MargaretKrutikova commented 2 years ago

I am not sure, I was looking at this example. And the execution of insert many, which doesn't seem to return anything at all.

JordanMarr commented 2 years ago

Ahh ok, I bet it does not support automatically returning identity fields for multiple inserts, which seems reasonable. That is probably why the overload for multiple inserts doesn't allow for the returnId field.

SqlHydra could probably support this, but for v1 of this feature, I think it would be best to throw the following exception: "getId is not currently supported for multiple inserts via the entities operation."

JordanMarr commented 2 years ago

v0.530.0 has been published with multiple inserts via entities operation. Note that you since getId is not supported for multiple inserts, you will need to replace it with excludeColumn.

MargaretKrutikova commented 2 years ago

Was able to test it recently, everything works as expected. Thanks a lot for your help! This project looks like a fantastic solution to the eternal problem of having your database in sync with the code and avoiding tons of boilerplate, more robust and stable than data providers ❤️