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?

Sconce

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.

Rolanderolando

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..?) – Sconce

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. – Rolanderolando

When will EF get spatial type support?? VS'2012; only 4 years after they were added to SQL Server?? – Ruelu

As a note EF 5 has support for Spacial data types. Not sure however if this works with MySQL or other DB providers. – Spalding

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.

Ruelu

This has been something that i've been playing / doing, with L2S. – Maxima

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. – Vying

Answer 3

You can also definitely do Linq-to-SQL with hand-written tables and columns and get the SQL spatial types directly. I tested the following on a sample DB (dont' forget to include reference and 'using' to System.SqlServer.Types

...

string connectionString = @"Data Source=YADDAYADDA;Initial Catalog=MUMBLEMUMBLE;Integrated Security=True";
var pointsFileDc = new PointsFileDC(connectionString);
var geos = (from point in pointsFileDc.pointsData
            select point).Take(10);
foreach (var geo in geos)
{
    ObjectDumper.Write(geo);
}

...

public class PointsFileDC : DataContext
{
    public Table<GeoPoints> pointsData;
    public PointsFileDC(string connection)
        : base(connection)
    {
    }
}

[Table(Name = "Points")]
public class GeoPoints
{
    [Column(IsPrimaryKey = true)]
    public int PointId;
    [Column]
    public SqlGeography GeoPoint;
}

Edmonson