In Entity Framework, how can I give arguments to the DbSet.SqlQuery method?

asp.net-mvc c# entity-framework

Question

I'm attempting to run a RAW SQL query that requires specific arguments in Entity Framework. I'm utilizing a technique from zzz-5 to zzz.

I'm not sure how to build the params object array. parameters for the object

My code block is shown 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);
        } 

It did not work when I attempted to create an ObjectParameter and add it to an object array. Can someone provide me an example of how to create the parameters for the params object?

Thanks! Flea

1
17
5/11/2012 2:35:01 PM

Accepted Answer

In order to avoid having my method's arguments match my database fields, which was not very apparent, I had to tidy up my method's parameters. That didn't work since the ObjectParameter does not allow the @ sign. I finally came up with the following answer:

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);

}

Additionally, I had to pick * in my sub-select since it was attempting to map to my object but was unable to do so because I was only returning one column. I was able to get this to work for me.

In the end, the whole object[] arguments may be implemented as follows:

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

Thanks for your assistance, hwcverwe.

Flea

16
12/12/2017 8:03:59 AM

Popular Answer

The answers given above are accurate, however because SqlQuery's signature is SqlQuery(sql:String, params object[] arguments), you may make the code clearer and more readable by using:

context.SqlQuery(sql, latParam, lngPara);

It wouldn't even need to be arrayed before this would be entirely lawful.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow