Linq & unsupported data types (Geography)

Question

So, Linq does not support the Geography data type, which throws a major spanner in the works in the lovely 'drag table onto the Linq design surface' developemnt model.

Is there any way that I can extend Linq to work with the Geography datatype? Or will I need to build a whole new datalayer and set of queries for whenever I need to use Geography columns?

I've been stuck on this for a few days and can't work out if it's possible.

Answer 1

Cast the column to a varbinary(max), which Linq to SQL can handle. One way to avoid doing this in every query is just to add a computed column defined as CAST(GeographyColumn AS varbinary(max)).

Once you have the byte[] data, you can write a short utility method to convert it to the actual Microsoft.SqlServer.Types.SqlGeography class using a MemoryStream and the IBinarySerialize.Read/Write methods.

As far as I know, this is the only working solution if you need to work with any CLR type, including geography, geometry, hierarchyid, and any custom types - Linq doesn't "natively" support any of them. It's a bit of a pain to write all the boilerplate code, but you can make it easier with a few extension methods.

You won't be able to query against the column this way; however, you can get what I would call halfway there using the Linq Dynamic Query Library. You won't have intellisense or some of the other Linq goodies, but it's still composable and therefore better than hand-crafting every query, and you can get strong-typed results.

Update: I found a slightly cleaner solution here. You can use the designer this way; just add the SqlGeography wrapper property to a partial class and use the STGeomFromWKB method with the SqlBytes class. That still won't give you inline query capabilities, though.