CODEWITHSUNDEEP
entity-framework-core
RobC
RobC

Reputation: 1415

Correct syntax for DbContext.Database.ExecuteSqlRaw()

I've been frustrating myself for too long trying to get the syntax for an Insert correct and am hoping someone can point out my error. I'm using EF Core 5.0.1.

I am trying to insert on the following table using raw SQL:

CREATE TABLE [dbo].[MyTable](
    [MyTableId] [smallint] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [UpdateDt] [datetime] NOT NULL,
    [UpdateBy] [nvarchar](30) NOT NULL,
    [CreateDt] [datetime] NOT NULL,
    [CreateBy] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_C_MyTable] PRIMARY KEY CLUSTERED 
(
    [MyTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I've tried this:

dbContext.Database.ExecuteSqlRaw($"INSERT INTO MyTable(Description, UpdateDt, UpdateBy, CreateDt, CreateBy) VALUES(@Description, @UpdateDt, @UpdateBy, @CreateDt, @CreateBy)", new[] { "status description", "2021-01-17 10:47:45.760", "updater", "2021-01-17 10:47:45.760", "creator" });

I also tried this:

db.Database.ExecuteSqlRaw($"INSERT INTO MyTable (Description, UpdateDt, UpdateBy, CreateDt, CreateBy) VALUES(@Description, @UpdateDt, @UpdateBy, @CreateDt, @CreateBy)", "status description", "2021-01-17 10:47:45.760", "updater", "2021-01-17 10:47:45.760", "creator");

Running either of these gives me the following exception:

System.TypeInitializationException : The type initializer for 'MyClass' threw an exception.
    ---- Microsoft.Data.SqlClient.SqlException : Must declare the scalar variable "@Description".

I have a hunch I'm overlooking something that's going to make me want to slap my forehead later, but am hoping someone can see what I'm doing wrong.

Upvotes: 0

Views: 996

Answers (1)

Rashik Hasnat
Rashik Hasnat

Reputation: 327

            dbContext.Database.ExecuteSqlRaw(
            $"INSERT INTO MyTable(Description, UpdateDt, UpdateBy, CreateDt, CreateBy) VALUES({0}, {1}, {2}, {3}, {4})",
            new[]
                {
                    "status description", "2021-01-17 10:47:45.760", "updater", "2021-01-17 10:47:45.760", "creator"
                });

This should work. The problem with your solution is that @description, @updatedDt doesn't work as a placeholder. Please follow this documentation here .

Upvotes: 1

Related Questions