views:

1131

answers:

4

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?

+7  A: 

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.

Alex James
Thanks Alex, seems like a reliable and definitive answer! Is there any particular reason EF4 won't support them (too hard, not enough demand, too low a priority..?)
Colin Desmond
Well I'm not going to say too low a priority. I consider it a high priority. But I will say it wasn't as high as some of the things we ended up doing, like POCO, FKs, Model First etc.
Alex James
When will EF get spatial type support?? VS'2012; only 4 years after they were added to SQL Server??
Chris Pietschmann
+15  A: 

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.

Chris Pietschmann
Excellent, thank you very much Chris. I'll take a look at this!
Colin Desmond
This has been something that i've been playing / doing, with L2S.
Pure.Krome
Thank you very much :)
Aggelos Mpimpoudis
We store this stuff as Lat/Lng float columns, and use triggers to create the geography column value. That lets us have it both ways - types that are trivial to get/set, and spatial querying in SPs.
Peter Wone
A: 

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.

Steve
This isn't an answer.
David Pfeffer