Cannot map SQL Geography column to EF DbGeography class with dapper

Question

Cannot map SQL Geography column to EF DbGeography class with dapper

c# dapper entity-framework sql sqlgeography

Question

I have table with sql geography column in my SQL database. I have generated entities for my database with EF6. As you know Entitiy Framework generates System.Data.Entity.Spatial.DbGeography for SQL Geography type. I'm using dapper to run queries and map the results into my EF generated entities.

My entity class

public partial class Fix
{
    public Fix()
    {
        this.FixUsers = new HashSet<FixUser>();
    }

    public long FixID { get; set; }
    public long UserID { get; set; }
    public System.Data.Entity.Spatial.DbGeography Position { get; set; }
    public int Radius { get; set; }
    public System.DateTime CreatedDate { get; set; }

    public virtual MemberProfile MemberProfile { get; set; }
    public virtual ICollection<FixUser> FixUsers { get; set; }
}

SQL query which throw an exception

    var fix = SqlConnection.Query<Fix>(@"SELECT TOP(1) 
                                         f.FixID as FixID, 
                                         f.UserID as UserID, 
                                         f.Radius as Radius, 
                                         f.CreatedDate as CreatedDate, 
                                         f.Position as Position
                                         FROM [Fix] f 
                                         WHERE f.FixID = @fixId", new { fixId }).FirstOrDefault();

Here is exception snapshot

enter image description here

I think by default dapper is trying to map to Microsoft.SqlServer.Types.SqlGeography.

Is there any workaround here?

EDITED

Found some solution, created partial class for my entity

public partial class Fix
{
    public string PositionString
    {
        set
        {
            Position = DbGeography.PointFromText(value, 4326);
        }
    }
}

And changed my query

var fix = SqlConnection.Query<Fix>(@"SELECT TOP(1) 
                                     f.FixID as FixID, 
                                     f.UserID as UserID, 
                                     f.Radius as Radius, 
                                     f.CreatedDate as CreatedDate, 
                                     f.Position.ToString() as PositionString
                                     FROM [Fix] f 
                                     WHERE f.FixID = @fixId", new { fixId }).FirstOrDefault();

Mkrtich Mazmanyan

Popular Answer

If you really want to use Dapper, you can convert SqlGeography into DbGeography:

DbGeography.FromText(sqlGeo.ToString());

So just do the conversion in memory, or you can also just use SQL with EF:

dbContext.Fixes.SqlQuery("SELECT TOP(1) 
                                     f.FixID, 
                                     f.UserID, 
                                     f.Radius, 
                                     f.CreatedDate, 
                                     f.Position
                                     FROM [Fix] f 
                                     WHERE f.FixID = @fixId", new SqlParameter("fixId", fixId)).FirstOrDefault();

or just use Entity Framework the normal way? Since it's not a complicated query :)

dbContext.Fixes.Find(fixId);

I am still curious why you query with Dapper to then map it to Entities from EF

deherch

View more on Stack Overflow

Marc Gravell · Accepted Answer · 6/4/2014 9:21:44 AM

Dapper has inbuilt support for many common data types, but not all. You might consider using a custom query parameter - you can see how from this commit which adds custom support for table valued parameters as DataTable. I would be very reluctant to add anything that demands additional dependencies, especially for things like EF. What perhaps might be useful in the future is a customisable registration tool for custom providers (allowing the data in the parameters to be anything - moving where the map happens). This does not exist today though.

Cannot map SQL Geography column to EF DbGeography class with dapper

Question

Fastest Entity Framework Extensions

Expert Answer

Popular Answer

Performance

Expression Evaluator

Related