Dapper unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Data.Entity.Spatial.DbGeography'

Question

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

 public DbGeography Location { get; set; }

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();

I get the following error:

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'.

What is causing this?

Update

Just for kicks, I tried using EF:

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

And I get a different error:

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

goes bald

Conclusion

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.

@Oluwafemi - I see. So... How do I make EF and Dapper play nice with the above query? — SB2055
– SB2055, Commented Sep 11, 2016 at 21:46
EF should handle geography types (but I don't use them at all). Last error seems to be unrelated to geography (but it could be an EF bug when you use geography). Does User work fine if you use context.Users.ToList()? — bubi
– bubi, Commented Sep 13, 2016 at 13:54
@bubi EF handles it fine if I use DbGeography - the issue is Dapper mapping to that type, which is EF-specific. — SB2055
– SB2055, Commented Sep 13, 2016 at 19:17

Marc Gravell · Accepted Answer · 2016-09-13 19:38:33Z

7

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();

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.

answered Sep 13, 2016 at 19:38

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SB2055 Over a year ago

Works perfectly. We're now on spatial! Thanks Marc.

JasonCoder Over a year ago

Further steps and explanation: github.com/StackExchange/Dapper/issues/570

Collectives™ on Stack Overflow

Dapper unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Data.Entity.Spatial.DbGeography'

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related