When using DbSet<T>.SqlQuery(), how to use named parameters?

Question

I'm a big fan of using named parameters instead of string-based parameter injection. It's type-safe and safe against most forms of SQL injection. In old ADO.NET, I would create a SqlCommand object and a bunch of SqlParameters for my query.

var sSQL = "select * from Users where Name = @Name";
var cmd = new SqlCommand(conn, sSQL);
cmd.Parameters.AddWithValue("@Name", "Bob");
cmd.ExecuteReader();

Now, in Entity Framework, it appears (on this link) to have regressed to a simple String.Format statement and string injection again: (simplified for discussion)

MyRepository.Users.SqlQuery("Select * from Users where Name = {0}", "Bob");

Is there a way to use named parameters with the Entity Framework DbSqlQuery class?

gdoron · Accepted Answer · 2012-10-17 01:02:54Z

4

var param = new ObjectParameter(":p0", "Bob");
MyRepository.Users.SqlQuery("Select * from Users where Name = :p0", param);

answered Oct 17, 2012 at 1:02

gdoron

151k59 gold badges302 silver badges376 bronze badges

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

3 Comments

Eric Falsken Over a year ago

Where is that documented? And what name formats are allowable in the SQL Body? Can it not support the @name style?

gdoron Over a year ago

@EricFalsken. You can see in here in MSDN, this \@name style are supported.

NibblyPig Over a year ago

This totally doesn't work for me, it is not documented, and you just get

The specified parameter name ':p0' is not valid. Parameter names must begin with a letter and can only contain letters, numbers, and underscores.

Brian Hudell · Accepted Answer · 2014-03-25 17:46:49Z

3

Since I can't comment, I'm fixing the other answer:

var param = new ObjectParameter("p0", "Bob");
MyRepository.Users.SqlQuery("Select * from Users where Name = :p0", param);

You don't have to put a colon on the name when instantiating an ObjectParameter. That's why SLC got the error he mentioned in his comment.

answered Mar 25, 2014 at 17:46

Brian Hudell

771 silver badge7 bronze badges

Collectives™ on Stack Overflow

When using DbSet<T>.SqlQuery(), how to use named parameters?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related