How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?
Solution 1
Turns out that this works.
var firstName = "John";
var id = 12;
var sql = "Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
Solution 2
Try this:
var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
ctx.Database.ExecuteSqlCommand(
sql,
new SqlParameter("@FirstName", firstname),
new SqlParameter("@Id", id));
Solution 3
You can either:
1) Pass raw arguments and use the {0} syntax. E.g:
DbContext.Database.SqlQuery("StoredProcedureName {0}", paramName);
2) Pass DbParameter subclass arguments and use @ParamName syntax.
DbContext.Database.SqlQuery("StoredProcedureName @ParamName",
new SqlParameter("@ParamName", paramValue);
If you use the first syntax, EF will actually wrap your arguments with DbParamater classes, assign them names, and replace {0} with the generated parameter name.
The first syntax if preferred because you don't need to use a factory or know what type of DbParamaters to create (SqlParameter, OracleParamter, etc.).
Solution 4
The other answers don't work when using Oracle. You need to use :
instead of @
.
var sql = "Update [User] SET FirstName = :FirstName WHERE Id = :Id";
context.Database.ExecuteSqlCommand(
sql,
new OracleParameter(":FirstName", firstName),
new OracleParameter(":Id", id));
Solution 5
Try this (edited):
ctx.Database.ExecuteSqlCommand(sql, new SqlParameter("FirstName", firstName),
new SqlParameter("Id", id));
Previous idea was wrong.
Related videos on Youtube
jessegavin
I am a Senior Software Developer at Dow Jones. Formerly vmware, Thomson Reuters.
Updated on January 22, 2022Comments
-
jessegavin almost 2 years
Let's just suppose I have a valid need for directly executing a sql command in Entity Framework. I am having trouble figuring out how to use parameters in my sql statement. The following example (not my real example) doesn't work.
var firstName = "John"; var id = 12; var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id"; ctx.Database.ExecuteSqlCommand(sql, firstName, id);
The ExecuteSqlCommand method doesn't allow you to pass in named parameters like in ADO.Net and the documentation for this method doesn't give any examples on how to execute a parameterized query.
How do I specify the parameters correctly?
-
jessegavin over 12 yearsWhen I do that I get the following error: "No mapping exists from object type System.Data.Objects.ObjectParameter to a known managed provider native type."
-
Ladislav Mrnka over 12 yearsSorry my mistake. Use DbParameter.
-
jrummell over 12 yearsDbParameter is abstract. You'll have to use SqlParameter or use a DbFactory to create a DbParameter.
-
Greg Biles about 12 yearsThis will work, but this mechanism allows for SQL injection and also prevents the database from reusing an execution plan when the statement shows up again but with different values.
-
Josh Gallagher almost 12 years@GregB I do not think you are correct here. I have tested that it won't allow me to, for instance, terminate a string literal early. Moreover, I looked at the source code and found that it's using DbCommand.CreateParameter to wrap up any raw values into parameters. So no SQL injection, and a nice succinct method invocation.
-
Greg Biles almost 12 years@JoshGallagher Yes, you're right. I was thinking of a string.Format scenario putting this together.
-
Andrew Barber about 11 yearsAny chance you could explain this code and why it is an answer to the question posed, so that those who come and find this later can understand it?
-
Neco about 10 yearsProblem with {0} syntax that you loose readability - i personally do not really like it. Problem with passing SqlParameters that you need to specify concrete implementation of DbParameter (SqlParameter, OracleParameter etc.). The provided example allows you to avoid these issues.
-
Andrew Barber about 10 yearsI guess that's a valid opinion, but you have not answered the question actually being asked here; How to pass parameters to
ExecuteSqlCommand()
You should be sure to answer the specific question being asked when you post answers. -
Arnthor over 9 yearsIt does NOT work as of SQL Server 2008 R2. You'll have @p0, @p2, ..., @pN instead of parameters you passed. Use
SqlParameter("@paramName", value)
instead. -
Makotosan over 8 yearsUpvoted for mentioning that the {0} syntax is database agnostic. "... you dont[sic] need to use a factory or know what type of DbParamaters[sic] to create ..."
-
an phu over 8 yearsDownvoted because it is unnecessarily complicated (e.g. uses reflection, re-inventing the wheel, fails to account for different database providers)
-
Zar Shardan over 7 yearsUp-voted because it shows one of the possible solutions. I'm sure ExecuteSqlCommand accepts parameters the same way SqlQuery does.I also like Dapper.net style of passing the parameters.
-
Marek Malczewski almost 6 yearsIn SQL Server I use @p0 instead of :p0.
-
sansy over 5 yearsI am not able to pass more than 3 paramters. If I do, i get following error "Additional information: The parameterized query '(@param1 type1,@param2 type2,@param3' expects the parameter '@param4', which was not supplied." Any suggestion?
-
ANeves over 5 yearsThis is not db-agnostic, it will only work for MS-SQL Server. It will fail for Oracle or PG.
-
georgiaboy82 about 5 yearsSo ExecuteSqlCommand is just a glorified String.Format call that also executes the sql command it formats.
-
Joshua Kemmerer almost 5 yearsI love .NET Core 2.0 so much it gives me tears of joy :')
-
Paul Carlton over 4 yearsI can see some people's enthusiasm since .NET Core 2.0 has been a smoother ride for me so far.
-
CodeNaked about 4 yearsHow is the first one not vulnerable to SQL injection? Both use C# string interpolation. The first one wouldn't be able to preempt the string expansion, as far as I know. I suspect you meant that to be
ctx.Database.ExecuteSqlCommand("Update [User] SET FirstName = {firstName} WHERE Id = {id}", firstName, id);
-
Greg Gum about 4 years@CodeNaked, No, I did not mean that. EF is aware of the issue, and creates two actual parameters to protect you. So it is not just a string that is passed through. See link above for details. If you try it in VS, my version will not issue a warning about Sql Injection, and the other one will.
-
CodeNaked about 4 years@GregGum - TIL about
FormattableString
. You're right and that's pretty cool! -
FlyingV about 4 yearsDon't forget to do using System.Data.SqlClient
-
FlyingV about 4 yearsDon't forget to do using System.Data.SqlClient
-
Daniel about 4 years@GregBiles and others: This overload is definitely NOT using string formatting, even though it looks like it. If you look at the sql in profiler, it is parameterized. .NET is doing the right thing in this case and changing {0} to `@p0, then parameterizing. It's a trade-off. It does look like evil string.format, but it is more concise (and db agnostic, see other answer below) than having to do new SqlParameter("@foo",paramvalue)
-
ScottB almost 4 yearsScenario 1 is deprecated in favour of an interpolated version. Equivalent is now: DbContext.Database.ExecuteSqlInterpolated($"StoredProcedureName {paramName}");
-
Chris Bordeman over 3 yearsThank god no one uses Oracle. Well not voluntarily! EDIT: Apologies for the late joke! EDIT: Apologies for the bad joke!
-
Flatlineato almost 2 yearsIn the first example I think a parenthesis is missing
-
Greg Gum almost 2 years@Flatlineato, Parenthesis added.