CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-core
Raphael Laurent
Raphael Laurent

Reputation: 1951

ExecuteSqlCommand returns 'invalid object name' error

I'm trying to execute raw SQL code in Entity Framework Core, but it returns the following error:

System.Data.SqlClient.SqlException: 'Invalid object name '#MyTempTable'.'

I tried running the following commands, all fail:

dbContext.Database.ExecuteSqlCommand(@"INSERT INTO #MyTempTable (BatchId, RowId) VALUES (1,1)");
dbContext.Database.ExecuteSqlCommand(@"INSERT INTO dbo.#MyTempTable (BatchId, RowId) VALUES (1,1)");

Both those commands work fine if I run them from SSMS directly.

What am I doing wrong?

Upvotes: 1

Views: 727

Answers (1)

Steve
Steve

Reputation: 216243

The statement INSERT INTO requires the destination table to exists.
In your code context this is false and you get the error.
A temporary table is dropped when the connection that created it is closed so it is perfectly possible that EF closed the connection between the point where you have created the temporary table and the point where you try to use it.

Without a lot more of context to understand the flow of your code you could write your query as 

select 1 as BatchID, 1 as RowID INTO #MyTempTable

INSERT INTO vs SELECT INTO

There is also this document on MSDN that hints at the possibility to manually opening and closing the connection under the EF dbContext

Upvotes: 2

Related Questions