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?

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jun 16, 2015 at 5:16
@marc_s I agree. I was just using it as a default stored procedure name — mko
– mko, Commented Jun 16, 2015 at 8:18

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related