Entity Framework Core: Type Udt is not supported on this platform. (Spatial Data - Geography)
asp.net-web-api c# entity-framework entity-framework-core
Question
I'm experimenting with entity framework core and stumbled upon an error I've never seen before and can't figure out how to fix it. I'm using .net Core Web API 2.0 with EntityFramework Core 2.00-preview2-final
Here is a simple example that triggers the error.
(concept: simple endpoint to get a user from database)
Error: System.PlatformNotSupportedException: Type Udt is not supported on this platform.
Any suggestions?
The problem is that I'm using geography in my database but I use it as a string in my model, because entity framework core doesn't support spatial data yet...
Any way to keep this cake tasty without getting rid of geography, cause it's an important feature?
Edit : See my answer for current solution
Accepted Answer
Ok here is how I solved it:
The purpose is to keep geography in Entity Framework Core (without using DbGeography)
1) I created a struct called Location:
public struct Location
{
public double Longitude { get; set; }
public double Latitude { get; set; }
}
2) Add it to your EF Entity Model
public class User
{
public Location Location { get; set; }
}
3) Hide it in your modelbuilder
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>().Ignore(x => x.Location);
}
4) Generate a Migration (Add-Migration migrationname)
5) Go to your migration file 1231randomnumbers1231_migrationname.cs and add the following (this way we create another column of type geography named Location) and then update your database (update-database):
migrationBuilder.Sql(@"ALTER TABLE [dbo].[User] ADD [Location] geography NULL");
6) (optional) I created a static class to update the db, handy if you have a Location column in mulple tables.
public static class GeneralDB
{
public static async Task UpdateLocation(DbContext ctx, string table, Location location, int id)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
string query = String.Format(@"UPDATE [dbo].[{0}] SET Location = geography::STPointFromText('POINT(' + CAST({1} AS VARCHAR(20)) + ' ' + CAST({2} AS VARCHAR(20)) + ')', 4326) WHERE(ID = {3})"
, table.ToLower(), location.Longitude, location.Latitude, id);
await ctx.Database.ExecuteSqlCommandAsync(query);
}
public static async Task<Location> GetLocation(DbContext ctx, string table, int id)
{
Location location = new Location();
using (var command = ctx.Database.GetDbConnection().CreateCommand())
{
string query = String.Format("SELECT Location.Lat AS Latitude, Location.Long AS Longitude FROM [dbo].[{0}] WHERE Id = {1}"
, table, id);
command.CommandText = query;
ctx.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
if (result.HasRows)
{
while (await result.ReadAsync())
{
location.Latitude = result.GetDouble(0);
location.Longitude = result.GetDouble(1);
}
}
}
}
return location;
}
}
This only works in EF Core 2.0
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
For EF Core 1.0 you would have to find an alternative way to replace a ',' with '.'. A good old fashion .Replace() method could do the job.
location.Longitude.ToString().Replace(',', '.')
7) CRUD Examples:
7.1: Read
public async Task<User> GetByIdAsync(int id)
{
User user = await ctx.User.AsNoTracking().SingleOrDefaultAsync(x => x.Id == id);
user.Location = await GeneralDB.GetLocation(ctx, "user", id);
return user;
}
7.2: Create
public async Task<User> CreateAsync(User entity)
{
ctx.User.Add(entity);
await ctx.SaveChangesAsync();
await GeneralDB.UpdateLocation(ctx, "user", entity.Location, entity.Id);
return entity;
}
7.3: Update
public async Task<User> UpdateAsync(User entity)
{
ctx.User.Attach(entity);
ctx.Entry<User>(entity).State = EntityState.Modified;
await ctx.SaveChangesAsync();
await GeneralDB.UpdateLocation(ctx, "user", entity.Location, entity.Id);
return entity;
}
Popular Answer
UPDATE: Since EF Core 2.2 there is support for spatial data!:
http://portainer/#/stacks/kolibry-acc_8ssg5qantkr2dgrbxsamsikf6
-------------------------
Eli, tnx for you're solution. For me it was almost the perfect solution. I had 2 problems:
Problems
- An other application also inserts directly into the database (temporary solution, this will be changed in the future).
- When getting the first 50 entities the data must be ordered by distance, so the 50 nearest entities will be returned.
Solutions
- Instead of updating the location table from code I use a trigger on my visit table. This trigger will fill or insert, delete or update the Location table. So the create, update, delete function don't have to do anything else than saving the entity.
create trigger VisitLocation_trigger on Visit
after UPDATE, INSERT, DELETE
as
if exists(SELECT * from inserted)
If exists(Select * from deleted)
BEGIN
-- UPDATE
UPDATE visit_location SET location = GEOGRAPHY::Point(Latitude, Longitude, 4326) FROM visit_location JOIN inserted ON visit_location.visitid = inserted.id
END
else
BEGIN
-- INSERT
INSERT INTO visit_location SELECT Id, GEOGRAPHY::Point(Latitude, Longitude, 4326) FROM inserted
END
else
BEGIN
-- DELETE
declare @visitId int;
SELECT @visitId = Id from deleted i;
DELETE visit_location WHERE visit_location.visitid = @visitId
end
- The query for getting the first 50 must be a raw sql query, that will look like this:
_context.Visit.FromSql(
"SELECT TOP 50 v.* " +
"FROM visit v " +
"INNER JOIN visit_location vl ON v.id = vl.visitid " +
"WHERE v.date > {0} " +
"AND GEOGRAPHY::Point({1},{2}, 4326).STDistance(Location) < {3} " +
"ORDER BY GEOGRAPHY::Point({1},{2}, 4326).STDistance(Location)",
startDate, latitude, longitude, radius).ToList();
CRUD
Read
public async Task<Visit> GetByIdAsync(int id)
{
return await _context.Visit.AsNoTracking().SingleOrDefaultAsync(x => x.Id == id);
}
public IList<Visit> GetLastVisitsForHouseIdsByCoordinates(DateTime startDate, double longitude, double latitude, long radius)
{
return
_context.Visit.FromSql("SELECT TOP 50 v.* " +
"FROM visit v " +
"INNER JOIN visit_location vl ON v.id = vl.visitid " +
"WHERE v.IsLastVisit = 1 " +
"AND v.date > {0} " +
"AND GEOGRAPHY::Point({1},{2}, 4326).STDistance(Location) < {3} " +
"ORDER BY GEOGRAPHY::Point({1},{2}, 4326).STDistance(Location)",
startDate, latitude, longitude, radius).ToList();
}
Create
public async Task<Visit> CreateAsync(Visit visit)
{
_context.Visit.Add(visit);
await _context.SaveChangesAsync();
return visit;
}
Update
public async Task<Visit> UpdateAsync(Visit visit)
{
_context.Visit.Attach(visit);
_context.Entry(visit).State = EntityState.Modified;
await _context.SaveChangesAsync();
return visit;
}
Delete
public async Task DeleteAsync(Visit visit)
{
_dbContext.Remove(entityToUpdate);
_context.Entry(visit).State = EntityState.Deleted;
await _context.SaveChangesAsync();
return visit;
}
The database models
public class Visit
{
public int Id { get; set; }
[Required]
public VisitStatus Status { get; set; }
[Required]
public double? Latitude { get; set; }
[Required]
public double? Longitude { get; set; }
public Location Location { get; set; }
[Required]
public DateTime Date { get; set; }
public string Street { get; set; }
public int? StreetNumber { get; set; }
public string StreetNumberLetter { get; set; }
public string StreetNumberLetterAddition { get; set; }
public string City { get; set; }
}
public struct Location
{
public double Longitude { get; set; }
public double Latitude { get; set; }
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow