After thinking about the issue with EF4 still not supporting spatial data, I emailed spatial data wizard, Jason Follas, and asked him his thoughts. He’s not too familiar with EF but said if EF supported blob types then you could just use the Microsoft.SqlServer.SqlGeometry type to read in the blob data from a stream.
I emailed back and said, well then if there’s a way to cast the geometry data, then we can create a view to provide the spatial data and stored procs to update it.
Then he and I both ended up with the same solution.
He created a table from scratch. I downloaded a sample table from CodePlex that has US Census Data containing in a SQL Server 2008 table that contains a geometry column.
Then we both came up with the correct cast (I floundered but let the error messages and some googling (oh wait, I think I was Binging :)) help me figure out what I was supposed to cast to) and the following view was born.
SELECT StateID, StateName, CONVERT([varbinary](MAX), geom, 0) AS Geo FROM dbo.State
Well, it was born twice. One twin in Vermont, the other in Ohio. 🙂
EF’s EDM Wizard happily reads in the view:
And has no problem executing queries and materializing the object:
using (var context=new myEntities())
{
var stateData = context.States.First();
var geoProperty = stateData.geomBlob();
}
Next is to take geoProperty and stream it into a SqlGeometry type (thanks again to Jason for pointing this out), and you are good to go.
If you want to do updates, then you’ll need some stored procs that you can pass the binary data into which will convert (on the server side) back into the geometry type. This is theoretical on my part. You should absolutely be able to do this. I just haven’t done it myself. Hopefully someone will try it out and let me know that it works! 🙂
And there’s no reason this shouldn’t work in both EF1 and EF4.
Querying is a different story. LINQ does not support querying the spatial data nor does ESQL. But EF supports query stored procedures if that can help. I really don’t know much about working with the spatial types so this perhaps Jason and I can continue to put our heads (and expertise) together on this.
I’m not working through part where we read in the SqlGeometry type because I don’t have the correct assembly on my computer. Jason has a blog post about getting the correct assembly for using the SqlGeometry type directly in .NET.
So, while the EDM doesn’t support the spatial types directly, it is still possible to work with them when using EF without too much inconvenience.
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!
In case Julie doesn’t post it, converting a byte[] array (the geomBlob) to an actual SqlGeometry (or SqlGeography) type is pretty straightforward:
// geoProperty should be a byte[]
var sqlGeom = new Microsoft.SqlServer.Types.SqlGeometry();
using (var stream = new System.IO.MemoryStream(geoProperty))
{
using (var rdr = new System.IO.BinaryReader(stream))
{
sqlGeom.Read(rdr);
}
}
The query issues are a particularly painful point for me, unfortunately. I’m currently working on a pretty substantial side project and, despite my love for the EF4, was forced to go with NHibernate for its NHibernate.Spatial functionality.
Reading and writing SQLGeography is all well and good, but if I have to pull all of my data into my application and run a local LINQ to Objects query on it, I lose the benefit of the spatial indices in SQL Server and I’m stuck querying over a few million records, one by one.
Nice work.
We have solved the similar problem (with PostGIS, not SqlGeometry). Take a look at this article:
http://www.devart.com/…/dotconnect
@David,
If you want to query NHibernate.Spatial on SQL Server 2008 using spatial indexes, you should use SpatialFilterCriterion.
How can I Insert spatial data using EF or Devart in an oracle DB?
I have absolutely no idea. Sorry try elsewhere?
Can we use the same solution to solve hid problem?
I have no idea. I can only suggest you test it yourself and if you do…report back! 🙂
I am doing the same thing with Oracle Spatial. I have not tried an insert yet but update works fine. What I did was create an updatable view that converts from SDO_GEOMETRY to OGC WKB and then EF maps as BLOB in the store model and Binary in the conceptual model. I attempted the property wrapper path as described in a blog on devArts forum. While I succeeded in building the conversion from binary to ERSI geometry within EF I could not serve that type up through ADO.Net Data Service and so moved that conversion down to the client. I am using a modified geometry converter from SharpMap to convert from WKB to ESRI Silverlight Geometry type. Geometry manipulation is done against the ESRI geometry type in Silverlight. We do not need to query spatially through the data service or EF at this point. Read only access to the data for display and record selection through AGS is much faster than through the ADO.net Data Service with or without a spatial query, measured in seconds in AGS compared to minutes. To edit an existing geometry we find it through AGS and then query an entity instance up through the data service by primary key, editting one record at a time. Yes, it is a bit convoluted but it fits within the sideboards of the project I am working on.