How to pass parameters to DbSet.SqlQuery Method in Entity Framework

Question

I am trying to execute a RAW SQL statement in Entity Framework which takes some parameters. The method I am using is from DbSet.SqlQuery

I am confused on how to construct the params object array: params object[] parameters

This is my code block here:

public ActionResult APILocation(string lat, string lng)
 {
    string SQL = "select * from (select Distance = ((ACOS(SIN(@lat * PI() / 180) * SIN(lat * PI() / 180) + COS(@lat * PI() / 180) * COS(lat * PI() / 180) * COS((@lng - Long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) from dbo.Stores) t where  Distance < 10 order by Distance asc";
            ObjectParameter latParam = new ObjectParameter("lat", lat);
            ObjectParameter lngParam = new ObjectParameter("lng", lng);

            object[] parameters = new object[] { latParam, lngParam };

            var stores = db.Stores.SqlQuery(SQL, parameters);

            return Json(stores, JsonRequestBehavior.AllowGet);
        } 

I tried creating ObjectParameter and putting it into an object array but that did not work. Can someone provide an example on how I should construct the params object[] parameters

Thanks! Flea

Answer 1

I ended up cleaning up my method's parameters so they wouldn't be the same as my database columns which was not very clear. The ObjectParameter does not support the @ symbol, so that didn't work. I ended up with the following solution:

public ActionResult APILocation(string latitude, string longitude)
{

 string SQL = "select * from (select *, Distance = ((ACOS(SIN({0} * PI() / 180) * SIN(lat * PI() / 180) + COS({0} * PI() / 180) * COS(lat * PI() / 180) * COS(({1} - long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) from dbo.Parish) t where Distance < 10 order by Distance asc";

     SqlParameter latParam = new SqlParameter("lat", latitude);
     SqlParameter lngParam = new SqlParameter("long", longitude);
     object[] parameters = new object[] { latParam , lngParam };

     var stores = db.Store.SqlQuery(SQL, parameters);

      return Json(stores, JsonRequestBehavior.AllowGet);

}

I also had to select * in my sub-select because it was trying to map to my entity which it couldn't since I was just returning one column and not everything. This solution proved to work for me!

Ultimately, the whole object[] parameters can be done like this:

 SqlParameter latParam = new SqlParameter("latitude", latitude);
 SqlParameter lngParam = new SqlParameter("longitude", longitude);
 object[] parameters = new object[] { latitude, longitude };

Thanks hwcverwe for your help.

Flea

Answer 2

The above answers are correct but since the signature for SqlQuery is SqlQuery(sql:String, params object[] parameters), you can simplify the code and give it a more natural feel by using:

context.SqlQuery(sql, latParam, lngPara);

This would be perfectly legal without having to array it first.

To reference the parameter values in the sql query, use @p0 and @p1 for values latParam and lngPara respectively.