Using SqlQuery in Entity Framework

Question

Is this really true that order of sql parameters in sql stored procedure must match the order of sql parameters used in SqlSquery:

 var Parameter = new List<SqlParameter>();
 Parameter.Add(new SqlParameter("@ParamOne", 1));
 Parameter.Add(new SqlParameter("@ParamTwo", 2));
 Parameter.Add(new SqlParameter("@ParamThree", 3));

context.Database.SqlQuery<myEntityType>("exec sp_Stored_Procedure @ParamOne, @ParamTwo, @ParamThree", Parameter.ToArray()).ToList<myEntityType>();

And in case sql developer decides to change stored procedure in the following way

ALTER PROCEDURE [dbo].[sp_Stored_Procedure]
@ParamOne as int,
@ParamTwo as int,
@ParamThree as int, 

AS ......

the result set will be empty?

This could raise many issues and I am asking is there something I am missing in this scenario?

Kris · Accepted Answer · 2015-06-16 08:44:09Z

1

I was oblivious to the situation at first!

Something like this should resolve your issue:

 var Parameter = new List<SqlParameter>();
 Parameter.Add(new SqlParameter("@p1", 1));
 Parameter.Add(new SqlParameter("@p2", 2));
 Parameter.Add(new SqlParameter("@p3", 3));

context.Database.SqlQuery<myEntityType>("exec sp_Stored_Procedure @ParamOne = @p1, @ParamTwo = @p2, @ParamThree = @p3", Parameter.ToArray()).ToList<myEntityType>();

answered Jun 16, 2015 at 8:44

Kris

1,39011 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kris · Accepted Answer · 2015-06-16 00:56:12Z

0

This could actually work BOTH ways.

If you have a query:

"exec sp_Stored_Procedure @p0, @p1, @p2"

Your parameters array will be assigned in order.

However if you have a query:

"exec sp_Stored_Procedure @username, @password"

Use an array of SqlParameter e.g. new SqlParameter("@password", userSuppliedPassword) then order is irrelevant.

answered Jun 16, 2015 at 0:56

Kris

1,39011 silver badges16 bronze badges

4 Comments

mko Over a year ago

There is no real difference between these two beside param name.

Kris Over a year ago

This article describes the variation in the parameters a little better: msdn.microsoft.com/en-us/library/gg696545(v=vs.113).aspx

mko Over a year ago

I have changed my code so you can better understand the problem

Kris Over a year ago

Oh i see what you're suggesting. I'll update my answer with a workaround.

Collectives™ on Stack Overflow

Using SqlQuery in Entity Framework

2 Answers 2

Comments

4 Comments

Your Answer

Collectives™ on Stack Overflow

Using SqlQuery in Entity Framework

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest