Named parameters in asp.net mvc 5 SQL query

Question

I have a big nasty SQL query to run in asp.net mvc 5, it's supposed to return a non-db-table object, and needs to be parameterized.

viz.

context.Database.SqlQuery<MyObject>("select a, b from table where a = @par")

where I can pass in the value of @par as a parameter

class MyObject {
  public a {get;set;}
  public b {get;set;}
}

For whatever reason the msdn docs assume that one cannot possibly want to execute any but the simplest of queries... https://msdn.microsoft.com/en-us/data/jj592907.aspx

NikolaiDante · Accepted Answer · 2016-02-16 21:40:29Z

4

Given:

var myPar = 1;

To use a parameter, try:

context.Database.SqlQuery<MyObject>("select a, b from table where a = @par", new SqlParameter("par", myPar));

Or:

context.Database.SqlQuery<MyObject>("select a, b from table where a = {0}", myPar);

answered Feb 16, 2016 at 21:40

NikolaiDante

18.7k14 gold badges84 silver badges121 bronze badges

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

Comments

Frank Fajardo · Accepted Answer · 2016-02-16 21:45:19Z

0

I prefer not to use SQL select strings to avoid potential SQL injection attacks. You can do this through Linq syntax:

 context.Database.table
     .Where(x => x.a == myPar)
     .Select(x => new MyObject { a = x.a, b = x.b });

answered Feb 16, 2016 at 21:45

Frank Fajardo

7,4291 gold badge31 silver badges49 bronze badges