public List <ParkingLot> GetParkingList(double lat, double lng)
{
List <ParkingLot> parkingList = new List <ParkingLot>();
string sql = $"SELECT p.*, dbo.fx_CalcDist(@lat, @lng, p.Latitude, p.Longitude) AS Distance FROM dbo.parkinglot p ORDER BY Distance";
using (var context = new MobileServiceContext())
{
try
{
// Get list of Parking Lots oredered by distance from the given position
// CollectionFromSql() is a custom Extension method of DbContext defined in Extensions.cs
// fx_CalcDist is a custom user defined database function to calculate distance
var mlist = context.CollectionFromSql(sql,
new Dictionary <string, object> {
{ "@lat", lat },
{ "@lng", lng },
});
foreach (IDictionary <string, Object> dict in mlist)
{
parkingList.Add(new ParkingLot
{
Id = (string)dict["Id"],
UpdatedAt = (DateTimeOffset)dict["UpdatedAt"],
CreatedAt = (DateTimeOffset)dict["CreatedAt"],
Name = (string)dict["Name"],
Street = (string)dict["Street"],
City = (string)dict["City"],
State = (string)dict["State"],
Zipcode = (string)dict["Zipcode"],
Telephone = (string)dict["Telephone"],
Timezone = (string)dict["Timezone"],
Latitude = (decimal)dict["Latitude"],
Longitude = (decimal)dict["Longitude"],
WorkHrsEnd = (int)dict["WorkHrsEnd"],
Capacity = (int)dict["Capacity"],
Reserved = (int)dict["Reserved"],
Distance = (double)dict["Distance"],
});
}
}
catch (Exception ex)
{
throw ServerUtils.BuildException(ex);
}
}
return(parkingList);
}