Entity Framework, spatial types, and manual edmx changes RRS feed

  • Question

  • I'm using EF for a location-driven database. Until recently I was simply using floats to encode latitude and longitude, but I read about the geography type and I'd definitely like to use a spatial index instead of putting indexes on the latitude/longitude columns.

    I've read all about the problems that EF has with the spatial type and I think I've read through just about every article on the subject, but none of the solutions seems very good. It seems like there has to be a straightforward and relatively pain-free way to encode and decode the latitude and longitude.

    I'm at the point in my project where a big database change (dropping the lat/lng columns in favor of a geography column) is easy because there's not much data, but there's enough code that changing my dependence on EF would be extremely painful. I'm really just looking for an easy way to extract the coordinates without changing much code.

    The most recent approach I considered was to map the location information into a view that extracted lat/lng and presented them as columns which I could map to an EF object. My location data have a lot of references based on foreign keys though and there would be a lot of manual work involved to re-create those relationships. This in itself isn't a big deal, but what bothers me about this approach is that I'd need to re-do all of this work every time I updated the model from the database since those kinds of changes are seemingly always overwritten.

    I was also thinking that I could add a stored procedure that I call after pulling my location out of the database that does the job of extracting the lat/lng, but I don't want to hit the database twice just because EF can't handle spatial data.

    Unfortunately I haven't worked with EF or SQL Server enough to have any intuition about what the quickest, most pain-free approach will be. I'm tempted to ignore the spatial type altogether despite its performance characteristics because it seems to be almost impossible to work with using EF. As an aside, I find it a little astonishing that the Entity Framework gets completely stymied by something that's now a core feature of SQL Server. There must be a way to deal with this.



    Monday, July 18, 2011 5:43 PM


  • Hello,

    unitl EFv4.2 is shipped you should avoid spatial types when working with EF because there is no simple way to work with them. Generally you will need your SQL server where spatial data are used, another layer of logic done from views and stored procedures to decompose and recompose spatial information so that EF doesn't know about it and EF layer where you will use lat. / long. directly without any knowledge of spatial types used internally.

    EFv4.2 should bring support for spatial types (it is currently in the first CTP so you can try the support) but it will take some time before it is shipped (there is no release date yet but I expect something like 6-12 months before go live licence).

    Best regards,

    Tuesday, July 19, 2011 8:16 AM