SqlGeography and Linq to Sql

I have a love-hate relationship with Microsoft SQL Server 2008’s Geography type.

I love working with it, but I hate it’s inaccessibility with some of my other favourite technologies. 

Specifically, I am trying to store GPS co-ordinates in the database as geography types.  This is a simple use, however, and although I’m only using points right now, we have other, more complex shapes that we must store.

I have figured out a couple of workarounds (that really aren’t workarounds at all) that allow me to use tables with the geography type in Linq to Sql.  These only really work for the point type and would get increasingly arduous as the shapes contain greater complexity.

Making SQL Server’s Geometry Play Nice with Linq to Sql

Unfortunately the Linq to Sql provider does not contain the data type support required for geometry or geography.  We’re not left with many options, at that point.

I tried a couple of hacks by creating the table by hand in the designer (it won’t let you drag the table onto the canvas) and stubbing in the field as a string, then banging up the code-behind to make it jive with the right types.

No go.  At the provider level it is not happy and there is no visible way to coax it into supporting the type.  Even if you cross your fingers AND your toes, you still get an error to the tune of:

The specified type 'geography' is not a valid provider type.

So, we’re stuck with less-pretty approaches.  I’m going with number one.

1. Use Lat and Long as float types in the tables.  This allows seamless use in the Linq-to-Sql editor.  Create views that represent those columns as a single geometry point for reading the data in non-Linq-to-Sql scenarios.
2. Store the co-ordinates in a geography field in the table and use stored procs to read and insert data as floats (lat and long).  Convert the data in the procs both ways.  Add the procs to your DBML.  This isn’t as good, because we lose some of the coupling in being able to ‘walk’ across your model in code.

I have also seen a recommendation to store the geo data as xml in the database and then do conversion on the client (casting to the proper types in code when needing to tap the data), or even pushing the data around as varbinary.  This approach would work, but generally I’m working with points right now and won’t need the added complexity.

Future Versions of Linq to Sql and Geometry

There have been suggestions from Microsofties on the forums that there will be support for these types down the road.  Right now, I’m sure it has to do with the fact that the assembly you need to access the type isn’t in the GAC by default and can’t be relied on to be present when someone’s in the throws of development (must have SQL Server installed to even get at the assembly in question).  Perhaps promoting the types to a more common distribution surface would alleviate this and open up the designer (and provider) to supporting them.

For now, Visual Studio 2010 Ultimate, Beta 2, does not have the support added.