c# - 通过 ado.net 插入 DBGeography 类型的正确方法是什么

Question

我正在尝试通过 ado.net 插入 DBGeography 类型，但没有成功。
这是我得到的错误:

No mapping exists from object type System.Data.Entity.Spatial.DbGeography to a known managed provider native type.

或:

Specified type is not registered on the target server.System.Data.Entity.Spatial.DbGeography, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

这就是我从数据库中获取它时所做的并且工作正常:

dynamic temp = reader.GetValue(3);

                var text = string.Format("POINT({0:R} {1:R})", temp.Long, temp.Lat);
                var srid = temp.STSrid.Value;
                this.Coordinates = System.Data.Entity.Spatial.DbGeography.PointFromText(text, srid);

但是插入不起作用:

updateCommand.Parameters.AddWithValue("@Coordinates", store.Coordinates);
// or ...
SqlParameter p = new SqlParameter();
                    p.ParameterName = "@Coordinates";
                    p.Value = store.Coordinates;
                    p.SqlDbType = System.Data.SqlDbType.Udt;
                    p.UdtTypeName = "geography";
                    updateCommand.Parameters.Add(p);

这里有什么问题吗？

Answer 1

DbGeography 是为 EntityFramework 而不是 ADO.NET 设计的类型。尝试通过 SqlGeography.Parse(SqlString) 将众所周知的文本模块解析为 SqlGeography方法，这应该可以解决您的问题。

dynamic temp = reader.GetValue(3);
var text = string.Format("POINT({0:R} {1:R})", temp.Long, temp.Lat);
var coordinate= SqlGeography.Parse(text );

SqlParameter p = new SqlParameter();
                 p.ParameterName = "@Coordinates";
                 p.Value = coordinate;
                 p.SqlDbType = System.Data.SqlDbType.Udt;
                 p.UdtTypeName = "geography";

updateCommand.Parameters.Add(p);

长话短说:

https://learn.microsoft.com/en-us/bingmaps/v8-web-control/modules/well-known-text-module

Well Known Text (WKT) is an Open Geospatial Consortium (OGC) standard that is used to represent spatial data in a textual format. Most OGC-compliant systems support Well Known Text. Spatial functionality in SQL Server 2008, 2012, and SQL Azure can easily convert between a spatial object in the database and WKT. A WKT can only store the information for a single spatial object and this spatial data format is usually used as part of a larger file format or web service response. The following are examples of each of the geometry types represented as Well Known Text and the equivalent Bing Maps class that is generated when parsing a Well Known Text string.