none
SQL 2008 and spartial data

    Question

  • Anybody try using spartial data (geometry or geography) in EF?

    EF support this?

    I was trying using nvarchar, varbinary types in scalar properties and later convert it in partial class.

    But it not working becouse when EF read object i get convert sqlgeo* to varbinary exception.

    Sunday, February 24, 2008 6:56 PM

Answers

  • Thank you, but i won:-)

    I read it as 'image' like this:

     

    Code Snippet

    <edmx:StorageModels>

    ....

    <EntitySet Name="Items" EntityType="DBModel.Store.Items" >

    <DefiningQuery>

    SELECT [ItemId], [Location].STAsBinary() as Location FROM Items

    </DefiningQuery>

    </EntitySet>

    <EntityType Name="Items">

    <Key>

    <PropertyRef Name="ItemId" />

    </Key>

    <Property Name="ItemId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

    <Property Name="Location" Type="image" />

    </EntityType>

    ...

    <edmx:StorageModels>

     

     

    And i make partial class like this:

     

    Code Snippet

    public partial class Item

    {

    public SqlGeography GeoLocation

    {

    get

    {

    return SqlGeography.STGeomFromWKB(new SqlBytes(Location), 4319);

    }

    set

    {

    Location = value.STAsBinary().Buffer;

    }

    }

    }

     

     

     

     

     

    Sunday, February 24, 2008 9:46 PM
  • Unfortunately, no, the EF will not support spatial data from SQL Server 2008 in EF v1.  Spatial data in SS08 is a kind of user-defined-type, and while UDTs are something that the EF will support in future releases they do present some unique challenges and in the end were postponed to a later release.

     

    - Danny

     

    Sunday, February 24, 2008 9:37 PM

All replies

  • Unfortunately, no, the EF will not support spatial data from SQL Server 2008 in EF v1.  Spatial data in SS08 is a kind of user-defined-type, and while UDTs are something that the EF will support in future releases they do present some unique challenges and in the end were postponed to a later release.

     

    - Danny

     

    Sunday, February 24, 2008 9:37 PM
  • Thank you, but i won:-)

    I read it as 'image' like this:

     

    Code Snippet

    <edmx:StorageModels>

    ....

    <EntitySet Name="Items" EntityType="DBModel.Store.Items" >

    <DefiningQuery>

    SELECT [ItemId], [Location].STAsBinary() as Location FROM Items

    </DefiningQuery>

    </EntitySet>

    <EntityType Name="Items">

    <Key>

    <PropertyRef Name="ItemId" />

    </Key>

    <Property Name="ItemId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

    <Property Name="Location" Type="image" />

    </EntityType>

    ...

    <edmx:StorageModels>

     

     

    And i make partial class like this:

     

    Code Snippet

    public partial class Item

    {

    public SqlGeography GeoLocation

    {

    get

    {

    return SqlGeography.STGeomFromWKB(new SqlBytes(Location), 4319);

    }

    set

    {

    Location = value.STAsBinary().Buffer;

    }

    }

    }

     

     

     

     

     

    Sunday, February 24, 2008 9:46 PM
  • Great!  We have certainly tried to make the EF flexible enough that there is more than one way to accomplish a task.  I'm glad that you've found a way to get your scenario working.

     

    - Danny

    Sunday, February 24, 2008 9:49 PM
  • Well done,

     

    That is so cool... so cool in fact I decided to write a post about it http://blogs.msdn.com/alexj/archive/2008/02/25/all-the-power-at-your-disposal.aspx

     

    Good work.

     

    Monday, February 25, 2008 8:11 PM
    Moderator
  • Thanks for the tip.

    I managed to massage the entity framework into reading my geography data.

    How can I add Insert/Update/Delete commands now? what is the syntax involved?

     

    Thanks in advance.

    Sunday, October 19, 2008 10:25 PM
  • If you chose this solution you have to write stored-procedures for inserting/updating/deleting with binary parameterSad

    But now I it read in edmx as binary like this (without DefiningQuery):
     
    Code Snippet

    <edmx:ConceptualModels>


    <Property Name="LocationData" Type="Binary" Nullable="true" MaxLength="2147483647" FixedLength="false" />


    <edmx:StorageModels>

    <Property Name="LocationData" Type="varbinary(max)" />


    And

    I my object write property:
    Code Snippet

    public SqlGeometry Location

    {
     get{ return LocationData == null ? null : SqlGeometry.STGeomFromWKB(new SqlBytes(LocationData), 0); }
     set {  LocationData = value.STAsBinary().Buffer; }
    }
     
    And now, you don't have to write stored procedures for inserting/updating/deleting operations :-) 
    If you want to write select queries "by location", you will have to write stored-procedure

    Sunday, October 19, 2008 11:04 PM
  • Thanks for the swift reply.

    I tried using this easier method, but I still have some problems -

    First of all, when the db gets the binary and automatically tries to convert it into geography datatype, it fails on accout that the srid is not valid. I checked it, and I see that the STBinary method does not record the srid of the SqlGeography (Point, in my case), and the STGeomFromWKB expects to recieve an srid as an argument. How can I tell my sql server to use the default 4326? I only guess the current default is 0, or some other invalid value for geography.

    Another thing I noticed, is that the STBinary method does not record the Z or M values of the geography. It even says so in the documentation. What's the idea here? My data includes altitude, and I would like to keep that info in the database as well. Is there some other way to easily manipulate geography columns in c# code with a 2-way mapping that keeps all the information?

    UPDATE:

    I now tried a different approch. In the StorageModels I changed the column to be of "nvarchar(max)" type. In the ConceptualModels I changed it into "String".
    I changed the get/set to work with STGeomFromText and ToString (which does keep the Z and M values).
    I was now able to add data into the db which contained all the data I was supplying.
    The problem now is with reading the data - the sql server can't automatically convert the geography column to nvarchar, like it did with the varbinary option.

    Is there some way "in between"? Why is it so hard - to read geography data, write geography data, and not drop any data (altitude, mainly) in the process?
    Monday, October 20, 2008 11:52 AM
  • At this point the questions you are asking are really more about sql server spatial than about the EF.   As such I would suggest posting your question in the sql server 2008 spacial forum just because that's where the folks who are more likely to be able to help hang out:

     

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1629&SiteID=1

     

    - Danny

     

     

    Monday, October 20, 2008 6:20 PM
  • I tried asking at the sql server 2008 forum, but haven't recieved any answer yet. I posted there a more description of my current problem - basically, I managed to insert data into the db with the EF, but not to read any data from it. Using varbinary nor nvarchar.

    Well, my more complete problem is in here, along with a link to my test solution. I hope someone in here can help me with my code.

     

    Thanks.

    Monday, October 27, 2008 9:15 PM
  • I think that this is a boo question all around. That fact you have to HACK a included type out of the box. Even if it is a UDT is a bad philosphy. I understand that you cant include everything under the sun but jeeze I mean if i can select it as a type in SSMS then its a type and should be included in EF and LToSql.
     

    blog.geoffreyemery.com
    • Proposed as answer by bigme Saturday, January 08, 2011 10:23 PM
    Friday, May 08, 2009 8:16 AM
  • Hello,

     

    Considering that many developers in this forum ask how to bring the clouds together: Azure+Bing Maps , my team has created a code sample for this frequently asked programming task in Microsoft All-In-One Code Framework. You can download the code samples at:

     

    CSAzureBingMaps  

     

    http://bit.ly/CSAzureBingMaps

     

    VBAzureBingMaps

     

    http://bit.ly/VBAzureBingMaps

     

    With these code samples, we hope to reduce developers’ efforts in solving the frequently asked

    programming tasks. If you have any feedback or suggestions for the code samples, please email us: onecode@microsoft.com.

    ------------

    The Microsoft All-In-One Code Framework (http://1code.codeplex.com) is a free, centralized code sample library driven by developers' needs. Our goal is to provide typical code samples for all Microsoft development technologies, and reduce developers' efforts in solving typical programming tasks.

    Our team listens to developers’ pains in MSDN forums, social media and various developer communities. We write code samples based on developers’ frequently asked programming tasks, and allow developers to download them with a short code sample publishing cycle. Additionally, our team offers a free code sample request service. This service is a proactive way for our developer community to obtain code samples for certain programming tasks directly from Microsoft.

    Thanks

    Microsoft All-In-One Code Framework

    Thursday, March 24, 2011 1:56 AM