Is it possible to use SqlGeography with Linq to Sql?

Question

I've been having quite a few problems trying to use Microsoft.SqlServer.Types.SqlGeography. I know full well that support for this in Linq to Sql is not great. I've tried numerous ways, beginning with what would the expected way (Database type of geography, CLR type of SqlGeography). This produces the NotSupportedException, which is widely discussed via blogs.

I've then gone down the path of treating the geography column as a varbinary(max), as geography is a UDT stored as binary. This seems to work fine (with some binary reading and writing extension methods).

However, I'm now running into a rather obscure issue, which does not seem to have happened to many other people.

System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Byte[]'.

This error is thrown from an ObjectMaterializer when iterating through a query. It seems to occur only when the tables containing geography columns are included in a query implicitly (ie. using the EntityRef<> properties to do joins).

System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()

My question: If I'm retrieving the geography column as varbinary(max), I might expect the reverse error: can't cast byte[] to SqlGeography. That I would understand. This I don't. I do have some properies on the partial LINQ to SQL classes that hide the binary conversion... could those be the issue?

Any help appreciated, and I know there's probably not enough information.

Extras:

• A geography column in the Visual Studio dbml Designer with 'Server Data Type' = geography generates this error: The specified type 'geography' is not a valid provider type.
• A geography column in the Visual Studio dbml Designer with no 'Server Data Type' generates this error: Could not format node 'Value' for execution as SQL.

Answer 1

Spatial types are not supported by Linq to SQL. Support is not "not great" - it's nonexistent.

You can read them as BLOBs, but you can't do that by simply changing the column type in Linq to SQL. You need to alter your queries at the database level to return the column as a varbinary, using the CAST statement. You can do this at the table level by adding a computed varbinary column, which Linq will happily map to a byte[].

In other words, some DDL like this:

ALTER TABLE FooTable
ADD LocationData AS CAST(Location AS varbinary(max))

Then, remove the Location column from your Linq to SQL class, and use LocationData instead.

If you then need access to the actual SqlGeography instance, you'll need to convert it to and from the byte array, using STGeomFromWKB and STAsBinary.

You can make this process a bit more "automatic" by extending the partial Linq to SQL entity class and adding an auto-converting property:

public partial class Foo
{
    public SqlGeography Location
    {
        get { return SqlGeography.STGeomFromWKB(LocationData, 4326); }
        set { LocationData = value.STAsBinary(); }
    }
}

This assumes that LocationData is the name of the computed varbinary column; you don't include the "real" Location column in your Linq to SQL definition, you add it in the ad-hoc fashion above.

Note also that you won't be able to do much with this column other than read and write to it; if you try to actually query on it (i.e. including it in a Where predicate) then you'll just get a similar NotSupportedException.