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

Behzad Bahmanyar · Accepted Answer · 2017-12-12 08:03:59Z

20

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

edited Dec 12, 2017 at 8:03

Behzad Bahmanyar

6,3154 gold badges37 silver badges41 bronze badges

answered May 11, 2012 at 17:00

Flea

11.3k6 gold badges76 silver badges88 bronze badges

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

2 Comments

Kugel Over a year ago

Your post makes no sense. You give your parameters names "latitude" and "longitude" but in the actual query you use "lat" and "Long". Moreover you name the SqlParameters "latParam" and "lngParam" but you don't use them afterwards.

Monsignor Over a year ago

To the ones who wonder how to define and use parameters correctly: In the SQL "Select @YourParamName", in code new SqlParameter("YourParamName", 12345);

zhulien · Accepted Answer · 2021-06-19 13:20:37Z

8

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.

edited Jun 19, 2021 at 13:20

zhulien

5,8154 gold badges25 silver badges41 bronze badges

answered Nov 18, 2015 at 23:02

user1210708

5234 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related