How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

c# sql ado.net linq-to-entities entity-framework-ctp5
electricsheep picture electricsheep · Feb 2, 2011 · Viewed 319.6k times · Source

I have a stored procedure that has three parameters and I've been trying to use the following to return the results:

context.Database.SqlQuery<myEntityType>("mySpName", param1, param2, param3);

At first I tried using SqlParameter objects as the params but this didn't work and threw a SqlException with the following message:

Procedure or function 'mySpName' expects parameter '@param1', which was not supplied.

So my question is how you can use this method with a stored procedure that expects parameters?

Thanks.

Answer

Devart picture Devart · Feb 2, 2011

You should supply the SqlParameter instances in the following way: 

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

Related questions

How do I extract data from a DataTable?

I have a DataTable that is filled in from an SQL query to a local database, but I don't know how to extract data from it. Main method (in test program): static void Main(string[] args) { const string connectionString = "server=…

Read More
Increasing the Command Timeout for SQL command

I have a little problem and hoping someone can give me some advice. I am running a SQL command, but it appears it takes this command about 2 mins to return the data as there is a lot of data. But …

Read More
Inserting values into a SQL Server database using ado.net via C#

I have created a simple program to insert values into the table [regist], but I keep getting the error Incorrect syntax near ')' on cmd.ExecuteNonQuery();: private void button1_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection("Data …

Read More