Linq-To-SQL & Geography Data Type

Hi,

I need help trying to figure out the best way to integrate the spatial data
type (specifically Geography) in an ASP.NET application that was built around
Linq-To-SQL.

Specifically, I have a table X that I’ve used the VS2008 Linq Designer to
import into my project. X is used in joins in various other Linq-To-Sql logic
throughout the application, so it’s important that X remains available as a
Linq object.

The problem arises when I introduce the Geography column to the X table that
the X linq object is built from. When I try to import the table into the
VS2008 Linq designer, I get the following error: "One or more selected items
contain a data type that is not supported by the designer."

I understand why I’m getting the error, I’m just not sure how I’m suppose to
work around it since I need the Linq X object, but I also need the geography
data type.

One possible solution that I’ve seen thrown about is to create a View of
table X that excludes the spatial column (or calls the ToString() function of
the Geography column so that Linq can read it). Is this the only solution or
is there something more elegant?

I was thinking that maybe I create a one-to-one relationship with another
table called XLocation that contains the Geography data type and constrains
the foreign key to X with a unique index to force a 1 to 1 relationship. Not
sure if I like that idea any better.

Any thoughts, hints, links to best practices, or anything to that effect
would be deeply helpful and much appreciated.

- dg

One way to get the spatial data types in LINQ is to cast to
VARBINARY(MAX) and then deserialize on the client. Hopefully in the next
version those data types will be natively supported.

--
Plamen Ratchev
http://www.SQLStudio.com

Hi Plamen,

Thanks for the response. Could you point me to an example of how I could go
about this? If I drag the table onto the linq designer in VS 2008, I wouldn't
be able to do it there - do I need to manually modify the dbml file or do I
cast it out of a stored procedure call? The possibility seems interesting,
I'm just not connecting the dots as to where I would do the casting.

Thanks

- dg

"Plamen Ratchev" wrote:

> One way to get the spatial data types in LINQ is to cast to
> VARBINARY(MAX) and then deserialize on the client. Hopefully in the next
> version those data types will be natively supported.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>

You can do this in a view or stored procedure, just include the column as:
CAST(geography_column AS VARBINARY(MAX))

Then use the view/stored procedure as data source.

--
Plamen Ratchev
http://www.SQLStudio.com

Great! Ok, so I'm guessing I can manually wire up the Linq object by using a
partial class for the dbml file so that it will look and behave like a
auto-generated Linq object. You wouldn't happen to know the native (ADO?)
Geometry type in .net that I'll cast the VARBINARY back to would you?

Thanks again for the help!

- gd

"Plamen Ratchev" wrote:

> You can do this in a view or stored procedure, just include the column as:
> CAST(geography_column AS VARBINARY(MAX))
>
> Then use the view/stored procedure as data source.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>

SqlGeometry

You would have to use IBinarySerialize to do deserialization yourself:
http://msdn.microsoft.com/en-us/libr...serialize.aspx


--
Plamen Ratchev
http://www.SQLStudio.com

I'll go try it right now. Thanks for all the help!

- dg

"Plamen Ratchev" wrote:

> SqlGeometry
>
> You would have to use IBinarySerialize to do deserialization yourself:
> http://msdn.microsoft.com/en-us/libr...serialize.aspx
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>