Spatial data types support in Linq2Sql or EF4

Question

Does anyone know (ideally, with a reference), whether the VS2010 release of LinqToSQL or EntityFramework v4 will support queries over the SQL 2008 spatial data types?

Answer 1

In EF 4.0 you might be able to hack something together using a combination of custom functions and pretending the spatial types are really Binary types. This is something that I am thinking of mucking around with and trying out and adding to my tips series. But as yet even the hack is unproven. :(

And as for direct support, unfortunately neither L2S or EF v4 will support spatial types in the VS2010 timeframe.

Alex James

Entity Framework Program Manager.

Answer 2

Here's a workaround to get it working in Entity Framework / LINQ to Entities:

You can use a database View to return Well-Known-Text (using "geometry.ToString()" in the query) or Binary. Then once the resulting rows are returned, just convert the string/binary to a SqlGeometry object in .NET.

Here's a sample query used to build a View that converts a "Location" field of geometry type to a Well-Known-Text String:

SELECT ID, Name, Location.ToString() as Location FROM MyTable

Here's an example of querying the resulting entities that have a "Location" field that contains a Well-Known-Text or String representation of the "geography" object:

var e = new MyApp.Data.MyDataEntities(connectionString);
var items = from i in e.MyTables
            select i;

foreach (var i in items)
{
    // "Location" is the geography field
    var l = SqlGeography.Parse(i.Location);
    var lat = l.Lat;
    var lng = l.Long;
}

One additional thing, is you'll need to do any spatial based queries within Stored Procedures, since you don't want to pull ALL the data from the table into .NET in order to perform your own spatial query using LINQ.

This isn't an elegent as natively supporting SQL Spatial Types, but it'll get you running with Entity Framework and SQL Spatial simultaneously.

Answer 3

Just as a follow up to this response, I'm wondering how you go about putting any modified SqlGeometry objects back into SQL using this approach?

From what I've read on the web, people seem to be overlooking this key part of the data exchange loop. Or maybe I'm missing something...

Any advice is appreciated.