2

Problem / Situation

I have quite complex objects (with binary fields, huge texts,..), that I read from Database with EF Core. For those objects I try to generate insert statements (plain string text) to save them in some file and execute on different database later.

I have a custom insert statement creator written in C#, but I have to implement EACH possible field/data type and of course it is incomplete and faulty. Also I needed to manually combine records somehow to avoid doing an INSERT statement for each record and instead one INSERT with multiple VALUES.

So I would really like to use some library for this task or ideally EntityFramework, since I already use it to read the objects from the database.

What I need

Some library (or feature in EntityFramework) that I can just pass some objects and receive the corresponding INSERT statements for that objects.

grafbumsdi
  • 405
  • 3
  • 11
  • Is probably dealing with dataset and ado.Net commandbuilder an option? – Alexander Powolozki Jul 25 '19 at 07:04
  • thx for hint @AlexanderPowolozki ! biggest constraint there is that I need to specify a SelectCommand first and connect to the database again. According to documentation: "The DbCommandBuilder must execute the SelectCommand in order to return the metadata necessary to construct the INSERT, UPDATE, and DELETE SQL commands. As a result, an extra trip to the data source is necessary, and this can hinder performance." Also I am not 100% confident that I will be able to manipulate the DataSet as I wish to afterwards, but lets see. – grafbumsdi Jul 25 '19 at 08:17

1 Answers1

1

As far as I know, the EF Core methods to generate SQL from a query are mostly internal and implementation dependant (and depend on the provider: keep in mind that EF Core is not -directly- related to a specific database or SQL language)... for example, there's one method (using reflection) for 2.1 here: https://stackoverflow.com/a/44180537/68972 but does not directly work on the 3.0 preview versions (since the internal classes and private fields changed).

However, EF Core does log the generated SQL (using the LoggerFactory defined on the DI, if available, or you can actually inject a LoggerFactory manually) when you call SaveChanges, so you could make a custom ILogger implementation which takes the output (and parse it so that you know it's a SQL statement) and saves it to a external file, and go an insert the entities into a DbContext connected to a dummy database (using the same provider you expect on the actual database where you'll execute those insert statements later).

Jcl
  • 27,696
  • 5
  • 61
  • 92