locked
Why No LINQ To SQL or Entity Framework Support for Spatial Data Types??!? RRS feed

  • Question

  • I have a new project I'm working on and I thought this time I would use either LINQ to SQL or Entity Framework, but then I tried connecting to my database that uses SQL Spatial Data Types and neither could access fields using the SQL Spatial Data Types. This is crazy! Now instead of using either of these two awesome tools, I need to take the long road and write my own complete data access layer.

    I figured that Visual Studio 2010 / .NET 4 Beta 2 would support them, but no support their either.

    When will the SQL Spatial Data Types be supported in LINQ To SQL and/or Entity Framework??
    Microsoft MVP - Windows Live Platform
    Blog: http://pietschsoft.com | Web.Maps.VE - ASP.NET AJAX Virtual Earth Server Control
    Wednesday, November 25, 2009 1:01 PM

Answers

  • That link provides actual code for a workaround that I've seen explanations of without examples. I also worked out my own solution using the same technique of converting the spatial object to a data type that LINQ to Entities understands.

    Oh, and I prefer converting to Well-Known-Text instead of Binary, it's only 1 line of code to convert to SqlGeography instead of 2, plus it's human readable. Although it may be more efficient to use Binary with larger spatial object or a large volume of data rows.

    Here's the solution I worked out. I also posted it on StackOverflow .

    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.

     


    Microsoft MVP - Windows Live Platform
    Blog: http://pietschsoft.com  | Web.Maps.VE - ASP.NET AJAX Virtual Earth Server Control
    Wednesday, November 25, 2009 3:07 PM

All replies

  • Hi Chris,

    LINQ to spatial (or the lack thereof) has been brought up a couple of times, and the answer seems to always remain "not at the moment"... you might find the following thread helpful, which includes a proposed workaround:
    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/01e5c428-8959-479e-abf7-b5d505887b46

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, November 25, 2009 1:54 PM
    Answerer
  • That link provides actual code for a workaround that I've seen explanations of without examples. I also worked out my own solution using the same technique of converting the spatial object to a data type that LINQ to Entities understands.

    Oh, and I prefer converting to Well-Known-Text instead of Binary, it's only 1 line of code to convert to SqlGeography instead of 2, plus it's human readable. Although it may be more efficient to use Binary with larger spatial object or a large volume of data rows.

    Here's the solution I worked out. I also posted it on StackOverflow .

    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.

     


    Microsoft MVP - Windows Live Platform
    Blog: http://pietschsoft.com  | Web.Maps.VE - ASP.NET AJAX Virtual Earth Server Control
    Wednesday, November 25, 2009 3:07 PM
  • Still nothing on this?  A year later?  No native support for spatial types?  Not even an ETA?
    Craig Farrell - Technical Architect - Cogsdale Corporation 2009 Microsoft Dynamics GP Partner of the Year
    Monday, November 29, 2010 8:10 PM

  • Who can give an answer? It's very long now, I've been always concerned about it.
    Saturday, December 18, 2010 1:27 AM
  • Since this post comes up on the first page when googling linq spatial support, there is now a CTP with support for spatial types:

    http://blogs.msdn.com/b/adonet/archive/2011/06/30/announcing-the-microsoft-entity-framework-june-2011-ctp.aspx

    • Proposed as answer by AaronLST Tuesday, August 16, 2011 8:51 PM
    Tuesday, August 16, 2011 8:50 PM
  • But note this blog posting from the EF team: http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-release-candidate-available.aspx

    The Spatial, Enum, and other support have been moved into .NET 4.5, and are missing from EF 4.2.

     


    • Edited by rob_kent Thursday, October 13, 2011 7:52 AM
    Thursday, October 13, 2011 7:51 AM