Dapper无法将类型为"Microsoft.SqlServer.Types.SqlGeography"的对象转换为类型为"System.Data.Entity.Spatial.DbGeography"的对象 [英] Dapper unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Data.Entity.Spatial.DbGeography'

问题描述

我在User表上使用Location字段配置了EF:

I have EF configured with a Location field on my User table:

 public DbGeography Location { get; set; }

但是，当我使用以下命令查询User表时:

However when I query my User table with:

 user = connection.Query<User>("update [User] set LastOnline = @lastOnline output INSERTED.* where Username = @un",
                        new { lastOnline = DateTime.UtcNow, un = username }).First();

我收到以下错误:

Message =错误解析列122(Location = POINT(-118.2436849 34.0522342)-对象)源= Dapper StackTrace: 在Dapper.SqlMapper.ThrowDataException(Exception ex，Int32索引，IDataReader阅读器，对象值)中 d:\ Dev \ dapper-dot-net \ Dapper NET40 \ SqlMapper.cs:第4045行 在Deserialize4650b5f0-d037-49ad-802e-8a9be95e8496(IDataReader) 在Dapper.SqlMapper.d__11 1.MoveNext() in d:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1572 at System.Collections.Generic.List 1..ctor(IEnumerable 1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable 1源) 在Dapper.SqlMapper.Query [T](IDbConnection cnn，字符串sql，对象参数，IDbTransaction事务，布尔缓冲，Nullable 1 commandTimeout, Nullable 1 commandType)中 d:\ Dev \ dapper-dot-net \ Dapper NET40 \ SqlMapper.cs:行1443 在App.Services.BrowseService.GetProfiles(ProfileSearchDto query，String username，Boolean isAdmin)中 c:\ PROJECTS \ App \ App-MAIN \ App \ Services \ BrowseService.cs:第330行 InnerException:System.InvalidCastException HResult = -2147467262 Message =无法将类型为'Microsoft.SqlServer.Types.SqlGeography'的对象强制转换为类型 "System.Data.Entity.Spatial.DbGeography".

Message=Error parsing column 122 (Location=POINT (-118.2436849 34.0522342) - Object) Source=Dapper StackTrace: at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in d:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4045 at Deserialize4650b5f0-d037-49ad-802e-8a9be95e8496(IDataReader ) at Dapper.SqlMapper.d__111.MoveNext() in d:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1572 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in d:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1443 at App.Services.BrowseService.GetProfiles(ProfileSearchDto query, String username, Boolean isAdmin) in c:\PROJECTS\App\App-MAIN\App\Services\BrowseService.cs:line 330 InnerException: System.InvalidCastException HResult=-2147467262 Message=Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Data.Entity.Spatial.DbGeography'.

是什么原因造成的?

更新

为了踢球，我尝试使用EF:

Just for kicks, I tried using EF:

db.Database.SqlQuery<User>("bla bla")

我得到一个不同的错误:

And I get a different error:

Message =对象类型不存在映射 <> f__AnonymousTypef`2 [[System.DateTime，mscorlib，Version = 4.0.0.0， Culture = neutral，PublicKeyToken = b77a5c561934e089]，[System.String， mscorlib，版本= 4.0.0.0，文化=中性， PublicKeyToken = b77a5c561934e089]]到本地的已知托管提供程序 类型. Source = System.Data

Message=No mapping exists from object type <>f__AnonymousTypef`2[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type. Source=System.Data

秃顶

结论

我的应用需要按半径搜索，当前使用的是原始的经纬度/经纬度查询.我试图升级实现以使用空间类型，但是看来我的工具不支持这种情况.回到天真，我去.

My app needs to search by radius and currently uses a naive lat/long boxed query. I was trying to upgrade my implementation to use spatial types, but it looks like my tooling doesn't support this scenario. Back to being naive I go.

推荐答案

Dapper不支持核心库中的实体框架类型 ，以减少依赖项的数量.但是，它具有可扩展的类型处理程序模型，并且DbGeography的绑定包含在 软件包.添加完后，您需要致电:

Dapper doesn't support Entity Framework types in the core library, to reduce the number of dependencies. However, it has an extensible type-handler model, and bindings for DbGeography are included in the Dapper.EntityFramework package. Once you have added that, you need to call:

Dapper.EntityFramework.Handlers.Register();

要求加载项进行自身注册.然后它应该工作.如果遇到程序集不匹配异常，则应该能够使用程序集绑定重定向来解决它.这尤其适用于基础SqlGeography类型，其中SQL Server返回的元数据是与Microsoft.SqlServer.Types包中的元数据不同的版本.但是程序集绑定重定向可以正常工作.

to ask the add-in to register itself. And then it should work. If you get assembly mismatch exceptions, you should be able to resolve it with assembly binding redirects. This applies in particular to the underlying SqlGeography type, where the metadata that SQL Server returns is a different version to the metadata in the Microsoft.SqlServer.Types package. But an assembly-binding redirect works fine.

这篇关于Dapper无法将类型为"Microsoft.SqlServer.Types.SqlGeography"的对象转换为类型为"System.Data.Entity.Spatial.DbGeography"的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！