Edit
This feature is new in EF Core 2.2
Spatial Data now added to EF Core 2.2 (see documentation)
Before EF Core 2.2 versions use this:
Now you can use Microsoft.Spatial for geography and geometry spatial operations.
ofc , EntityframeworkCore does not support spatial, so you can not create a field with geography data type in codefirst, I suggest you to do this with pure SQL commends until EntityframeworkCore supports spatial in Q2 2017 (See this). If you don't know how I will tell you.
First of all you need to add a field with geography data type,
so you need to run this commend in one of migrations up classes :
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("ALTER TABLE [dbo].[Cities] ADD [Location] geography");
}
if you are using UnitOfWork you can update Location field after you inserted a record like this :
try
{
City city = new City
{
Title = creator.Title
};
_cities.Add(city);
_uow.ExecuteSqlCommand("UPDATE Cities SET Location = geography::STPointFromText('POINT(' + CAST({0} AS VARCHAR(20)) + ' ' + CAST({1} AS VARCHAR(20)) + ')', 4326) WHERE(ID = {2})", city.Longitude, city.Latitude, city.ID);
return RedirectToAction("Index");
}
catch
{
return View(creator);
}
And now if you want to find nearby cities, you can use this commend :
var cities = _uow.Set<City>()
.FromSql(@"DECLARE @g geography = geography::STPointFromText('POINT(' + CAST({0} AS VARCHAR(20)) + ' ' + CAST({1} AS VARCHAR(20)) + ')', 4326);
Select ID, Address, CreationDate, CreationDateInPersian, CreationDateStandard, CreatorRealName, CreatorUserID, ExLanguageID, IsActive, IsDeleted, Latitude, Longitude, ModifierRealName, ModifierUserID, ModifyDate, ModifyDateInPersian, ModifyDateStandard, PhoneNumbers, Summary, TimeStamp, Title, Image from Cities
ORDER BY Location.STDistance(@g) DESC;",
35.738083, 51.591263)
.Select(x => new AllRecordsViewModel
{
ID = x.ID,
Title = x.Title
})
.ToList();
return View(cities);
// result for nearest cities :
1.Tehran
2.Ankara
3.Paris
4.Washington DC
Remember! you should select all of records except that field with geography data type!
