3

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.

SB2055
  • 12,272
  • 32
  • 97
  • 202
  • http://stackoverflow.com/questions/23186832/entity-framework-sqlgeography-vs-dbgeography – Oluwafemi Sep 11 '16 at 21:45
  • @Oluwafemi - I see. So... How do I make EF and Dapper play nice with the above query? – SB2055 Sep 11 '16 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 Sep 13 '16 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 Sep 13 '16 at 19:17

1 Answers1

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900