Recently, I was working on an ecommerce website project where I needed to display nearby shops based on latitude and longitude. To achieve this, I wanted to write a query that would filter out nearby shop records from the database. Below, you can see the SQL query I've written to filter shop records based on their geographical coordinates.
In post we will discuess how we can solved error : "The element type used in 'SqlQuery' method is not natively supported by your database provider."
SELECT TOP 30 Id, ShopName, Email, About, Livelat, Livelng, LiveLocation, Picture,
SQRT(POWER(69.1 * (Livelat - @latitude), 2) + POWER(69.1 * (@longitude - Livelng) * COS(Livelat / 57.3), 2)) AS distance
FROM Shops
WHERE Status = 1
ORDER BY distance
As I'm using Entity Framework Core version 7, I opted to use the SqlQuery function for executing raw SQL queries.Below is my
Code:
[HttpGet]
public ActionResult<ResponseDTO<List<ShopNearByMeModel>>> GetShopNearbyMe(double latitude, double longitude, double radiusdistnce = 288895.288400)
{
List<ShopNearByMeModel> userCustomPleopleNearBies = new List<ShopNearByMeModel>();
try
{
FormattableString formattableQuery = $"SELECT * FROM (SELECT Top 50 Id,ShopName,Email,About,Livelat,Livelng,LiveLocation,Picture, SQRT(POWER(69.1 * (Livelat - {lat}), 2) +POWER(69.1 * ({lng} - Livelng) * COS(Livelat / 57.3), 2)) AS Distance FROM Shops where Status=1 ORDER BY Distance) mytable WHERE Distance <{radiusdistnce}";
userCustomPleopleNearBies = _context.Database.SqlQuery<ShopNearByMeModel>(formattableQuery).ToList();
}
catch (Exception ex)
{
return StatusCode((int)HttpStatusCode.InternalServerError, ex.Message);
}
return Ok(userCustomPleopleNearBies);
}
public class ShopNearByMeModel
{
public Guid Id { get; set; }
public string ShopName { get; set; }
public string Email { get; set; }
public string About { get; set; }
public double Distance { get; set; }
public double Livelat { get; set; }
public double Livelng { get; set; }
public string LiveLocation { get; set; }
public string Picture { get; set; }
}However, I encountered an error stating: "'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type."
Upon researching, I discovered that querying custom types with Database.SqlQuery or _context.Database.ExecuteSql is not supported in EF7.
After searching on Google, I learned that we can't query custom types with `Database.SqlQuery` or `_context.Database.ExecuteSql` in Entity Framework 7. If you want to use those functions, you need to upgrade to EF8.
However, if you want to use SQL queries with custom types in Entity Framework 7, you can create a table-valued function and then call that function in your Entity Framework code. Here is detail post in that you can have a look.
If you are working with Entity Framework version 2.1 or earlier, you can utilize the _context.ExecSQL function as demonstrated below:
For Entity Framework Core 2.1 you can use below code:
string query = "SELECT * FROM (SELECT Top 50 Id, ShopName, Email, About, Livelat, Livelng, LiveLocation, Picture, SQRT(POWER(69.1 * (Livelat - " + lat + "), 2) + POWER(69.1 * (" + lng + " - Livelng) * COS(Livelat / 57.3), 2)) AS Distance FROM Shops where Status=1 ORDER BY Distance) mytable WHERE Distance < " + radiusdistnce + "";
List<ShopNearByMeModel> users = _context.ExecSQL<ShopNearByMeModel>(query);