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

1
3
7/26/2017 8:36:46 PM

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;
}
8
7/6/2017 1:03:48 PM

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

  1. An other application also inserts directly into the database (temporary solution, this will be changed in the future).
  2. When getting the first 50 entities the data must be ordered by distance, so the 50 nearest entities will be returned.

Solutions

  1. 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
  1. 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; }
    }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow