SqlSpatial: Can it be true? No LINQ!

Question

Hi. I was referred to this forum as the best place to ask this. At first I thought I was doing something wrong -- and I still may be -- but maybe I can get some tips here.

Situation: I have tables in SQL Server 2008 that store map info using standard SQL Server 2005 data types and my C# geo-app uses LINQ to access  tables/data, often via an light-ORM-layer generated by Microsoft SqlMetal. Well, I've added the cool SQL spatial types to my tables, and as soon as I did.

1. SqlMetal warns me that it is dropping the fields:
"Warning : SQM1021: Unable to extract column '_Area_Polygon_UDT' of Table 'dbo.rc_Resources' from SqlServer because the column's DbType is a user-defined type (UDT)."

2. Visual Studio 2008 SP1 Object To Relational Designer / Entity Builder will no longer let me visualize/edit/include tables into the workarea because they now contain an unsupported data type.

Does anybody know how to leverage the spatial UDTs using C#/LINQ even if I have to modify or create access code by hand (in the DB or in C#).

I sure hope that I'm missing out on something :-)

Thanks!

Richard

---

Hubert-Associates

Answer 1

Hi Richard,

As I said, I'm no LINQ-expert, but I found the following threads that may answer the question (although you may not like the answer):

http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/df1dc078-0b96-4209-8032-e32d9ba81294/
http://social.msdn.microsoft.com/forums/en-US/sqlspatial/thread/d02efea2-11c8-4da3-878d-6cd9a0a2becf/

It seems that LINQ does not recognise UDT datatypes, which is how geometry and geography are implemented, so you'll either have to serialize them to binary (using STAsBinary()) or alternatively ditch LINQ and use good ol' fashioned SQL...

... but I'm no LINQ-expert, so hopefully somebody else may have a solution!

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Tanoshimi, can u use linq to sql to grab the data as binary (without the use of a stored proc) .. or do you need to have a stored procedure that returns the binary (which linq can do).

---

-Pure Krome-

Answer 3

Thanks Tanoshimi,

Well, I'm looking for any way to be able to query the spatial data types from LINQ/C# -- after all, the UDTs are available in C# already, as are their functions. Certainly, I could write a stored procedure that converts them to standard data types (byte streams etc.) but I already have the spatial data in that form in the DB since I was doing a (small) subset of the features offered by the spatial in SQL 2005.

Best regards,
R

---

Hubert-Associates

Answer 4

Ok easy work around until Spatial support gets added, hopefully in 4.0 .

1.Create a new View

2.Cast your Geo type to a VarBinary(MAX)

3.Write your linq query

4.Use this code to convert to SQLgeo in C#

var a = from item in db.LINQGIs

where item.Country == "France"

select item.geom;

byte[] geoarr = a.First().ToArray();

SqlGeometry geo = new SqlGeometry();

geo.Read(new System.IO.BinaryReader(new System.IO.MemoryStream(geoarr)));

Answer 5

Thank you for the interesting tip. Actually, I do something similar to this already, but this is a indeed a new twist. I'll look forward to any other tips and tricks with LINQ (or links as this evolves) and keep my fingers crossed!

Best regards,
R

---

Hubert-Associates

Answer 6

AviationPlanning said:

Ok easy work around until Spatial support gets added, hopefully in 4.0 .

1.Create a new View

2.Cast your Geo type to a VarBinary(MAX)

3.Write your linq query

4.Use this code to convert to SQLgeo in C#

var a = from item in db.LINQGIs

where item.Country == "France"

select item.geom;

byte[] geoarr = a.First().ToArray();

SqlGeometry geo = new SqlGeometry();

geo.Read(new System.IO.BinaryReader(new System.IO.MemoryStream(geoarr)));

Heh - kewl!

quick question -> does this code have any problems with the BinaryReader and the MemoryStream not getting disposed of manually? are they getting auto-disposed when the linq query finishes as they are then out of scope?

---

-Pure Krome-

Answer 7

Sorry didn't check for replies!

The code I posted was really quick for an example so I'm not sure that I would use that exact new(new( setup in a real app for readability reasons if nothing else.   With that said we are working with a byte array in memory with managed classes not being used async so it should be ok. 

Answer 8

Simple answer, store the data as Xml in the Database. In C# Make sure to use SqlGeography as your Type, when inserting the data call .AsGml().Value to get the Xml. Pass in the Gml as the Linq Xml. Now when querying call

geography

 

 

::GeomFromGml()

Answer 9

AFAIK, spatial support (really UDT suport) will not be added to either Entity Framework or L2S in 4.0. So any "workaround" will have a longer lifetime than you think.

Cheers,
Bob Beauchemin