Declare variable in SQL with Entity Framework Core for ExecuteSqlCommand()

Question

On my DB I have a star schema implemented which will be updated regulary with new, additional data. In order to achieve that I need to update my dimension tables first. From merge tables I check whether there are values in a specific column which aren't in the according dimension table. If so, I insert the values to the dimension table's corresponding column. Other important columns in the dimension table will be handled by default.

I have a query that is perfectly working when executed on my DB via MSSMS. The query:

INSERT INTO destination.table (columnIWantToAddValuesTo)                                
SELECT m.wantedValue 
FROM merge.table as m                                                                     
WHERE NOT EXISTS (
                  select o.column 
                  from origin.table as o 
                  where o.column = m.wantedValue
);

The problem I'm having is that I need to utilize an AzureFunction which executes code, which will execute multible inserts like the one in the upper query. (Stored proccedures are not an option) To achieve set goals I'm using C# with Entity Framework Core. My goal now is to call ExecuteSqlCommand() on my DbContext. For maintainability, reuseabilty and so on I want to create the upper query as dynamical as possible. Which led to this code:

var sqlText = "INSERT INTO @insertTable (@columnIWantToAddValuesTo) SELECT m.@wantedValue FROM @mergetable as m " +
              "WHERE NOT EXISTS (select o.@column from @origintable as o where o.@column= m.@wantedValue);";
          
    var count = dbContext.Database.ExecuteSqlCommand(sqlText, parameters: 
                                                   new SqlParameter("@insertTable","my.table"),
                                                   new SqlParameter("@columnIWantToAddValuesTo","value"),
                                                   ...);

Doing so I catch the Exception:

Microsoft.Data.SqlClient.SqlException: 'Must declare the table variable "@insertTable".'

Following this suggestion I added DECLARE @insertTable varchar(32); to the FRONT of my var sqlText, resulting in this exception:

Microsoft.Data.SqlClient.SqlException: 'The variable name '@insertTable' has already been declared. Variable names must be unique within a query batch or stored procedure.'

How, Why is this possible? First the declaration is missing, when adding it there is one declaration too much...

Further I've tried different approaches, mostly as found on here, including working with {0}, etc.
From what I've acquired I would assume that I have to split up the var sqlText and concat it with corresponding method parameters instead of Sql-Parameters. But that seems a little... unclear, ugly, unsafe to me, what about you guys?

Funfact: For readability I splitt var sqlText and concated it with +. Doing so in my project, ExecuteSqlCommand() has syntax problems. For a running implementation the string needs to be in one line... So concating sqlText with method parameters might open a new chapter.

You're declaring `@insertTable` as a `varchar`; you *can't* `INSERT` into a `varchar` you `INSERT` into tables. Variables also can't replace literals such as tables names; SQL isn't a scripting language. Normally if you want to do something like this is means you either have a [XY Problem](http://XYproblem.info) or a design flaw. — Thom A, Oct 27 '20 at 08:55
This has nothing to do with EF. What are you after is basically dynamic SQL with parts which cannot be parameterized. You have to build it with some form of string concatenation - similar to how EF Core generates SQL commands for queries/inserts/updates/deletes. For instance, you could use C# interpolated string, e.g. `var sqlText = $"..."` and instead of ` @insertTable` put `{insertTable}` wher `insertTable` is `string` variable. — Ivan Stoev, Oct 27 '20 at 10:07
Maybe you have chosen wrong library. In linq2db this is usual query via LINQ. — Svyatoslav Danyliv, Oct 27 '20 at 10:51

Declare variable in SQL with Entity Framework Core for ExecuteSqlCommand()

0 Answers0