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