Share via

Yes you can execute PARAMETERIZED sprocs with EF Fluent API Code Only (and more!)

  • Article

This is really an add-on to this post here.

Turns out most people were only thinking that you could execute SQL strings only as opposed to detailed parameterized SQL statements. That's not correct. It's all supported and approved.

Scenario 1. Using Parameterized SQL statements

So this covers selects as well as invoking sprocs. If you have named parameters in your sql string EF is more than happy to take them and set values based on some very simple rules.

  1. The SQL contains a parameter name value markup item (e.g. @ParamName) in the query iteself OR
  2. The SQL contains a .Net format string replacement token (e.g. {0}

So take the following sample SQL, "SELECT * FROM MyTable WHERE Column1 = @Value" then you would simple pass the query parameters via a DbParameter type (appropriate for your underlying DbProvider EF is using) to the Database.SqlQuery<T>(String sql, params Object[] parameters)  overload. Simply add an API to your DbContext based type resembling this->

 public IEnumerable<T> SqlQuery<T>(String sql, params SqlParameter[] parameters)
{
 return parameters == null 
 ? this.Database.SqlQuery<T>(sql)
 : this.Database.SqlQuery<T>(sql, parameters.Cast<Object>().ToArray()); // Removed covarient by creating Object array
}

Your code will now be passing parameterized sql just like the EF generated LINQ queries do. This method is preferred over string replacement (option 2) as it removes SQL injection attacks and speeds SQL queries as parameterized SQL tend to have execution plans cached by SQL Server.

To call the above method you should simply create the appropriate parameter type and supply it to EF.

var sql = "SELECT * FROM MyTable WHERE Column1 = @Value";
var parameters = new [] { new SqlParameter("@Value", SqlDbType.Varchar, 50) { Value = 'My Value' };
var results = context.SqlQuery<Foo>(mySql, parameters);

and Boom goes the dynamite! :)

Scenario 2. Changing Column Names from SPROCS

A lot of people seem to be complaining that the SqlQuery<T> method performs simple convention based mapping on the result set meaning that the column value in Foo will be mapped to a property named Foo on type T as defined in your code. Somehow everyone throws up their hands and says they can't use EF. NO that is not the case but you just need to think about what you are doing a little more and pull out some simple TSQL skills. Using sp_executesql with parameterized input gives you all the safety and power of raw SQL text (actually for those of you that don't know, even SQL text is actually turned into calls to sp_executesql by the SQL query parser so you're always stuck with it regardless!). Add in a little dash of table variables and a final SQL statement and you are set. No rewriting your sprocs to match your model; no changing your model to match your sprocs.

In this example I am using a simple select statement but just imagine that this was the select inside of your sproc and change the @sql to be "execute mySproc @value" and you are fine...

 --declare @sql nvarchar(max) = 'execute mySproc @value' EXAMPLE SPROC EXECUTE STATEMENT
declare @sql nvarchar(max) = 'select newid(),@SystemName'
declare @value nvarchar(max) = N'an input value;

DECLARE @ParmDefinition nvarchar(500) = N'@value nvarchar(500)';

-- define the table variable to hold the query output. In this case I could
-- have performed modification from the underlying sproc result set but I didn't

 declare @results TABLE(
SystemId uniqueidentifier,
SystemName nvarchar(500)
)

-- Execute the sproc and fill the table variable with the output
INSERT @results
execute sp_executesql @sql,@ParmDefinition, @value=@value

-- Return the values coerced to your desired shapre.
SELECT SystemID, SystemName [Name] FROM @results

 

 

 

 HE SHOOTS HE SCORES!! So engoy your EFing journey out there
folks and remember, think about your entire toolkit when solving problems!