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.

Because I’ve had a lot of conversations about spatial data types lately, I thought I would create a SQL Server table that contained one of every type then bring it into an entity data model using the wizard to see what happens. This is in EF4.

Here are screenshots of the table and the entity showing their property types.

This way you can see what is and isn’t supported. The wizard excluded four fields it still doesn’t recognize at all.

• geography
• geometry
• hierarchyid
• sql_variant

If you focus on the entity, you can compare the property names to the property types to see how different data types are treated. E.g. the xml property is a String and the real property is a Single.

The fields that use Max become properties whose Max Length property =”Max”