CODEWITHSUNDEEP
visual-studio-2010linq-to-sqlsqlgeography
Chin
Chin

Reputation: 12712

LINQ to SQL Designer and the Geography datatype

I am having a few problems loading a table into the designer. I get the following error.

One or more selected items contain a data type that is not supported by the designer

Would I be correct in assuming it is the geography type used in the table that is causing this error?

Any pointers much appreciated.

Upvotes: 8

Views: 4639

Answers (3)

jocull
jocull

Reputation: 21175

I have a project (a small one, granted) that grew to have new requirements for spatial types. I decided to see how easy it would be to upgrade to entity framework from LINQ to SQL.

It took me no more than 30 minutes. Make sure you have a source control backup before you start.

  1. Install-Package EntityFramework -Version {pick one}
  2. Delete your old .dbml file(s)
  3. Generate a new entity model and update your references to the new entities objects (this is easier if you already had a base repository or something wrapping LINQ to SQL). I went this route (database first / designer) because it was a simpler upgrade path from LINQ to SQL. You can try code first if you prefer, but... YMMV.
  4. Update your connection strings. Entity Framework has their own weird wrapper around traditional SQL connections. Mine is like this, yours might look different: metadata=res://*/;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS; Initial Catalog=MyDataBase; Integrated Security=True'
  5. Fix the compile errors. Most of them will be differences in Insert/DeleteOnSubmit and EF's Add/Remove or Add/RemoveRange. Transaction scopes may work differently (consider using context.Database.BeginTransaction instead of transaction scopes).
  6. Rebuild it once the compile errors are resolved.

That's it. It was much easier than I expected and now I can use the spatial types without any trickery.

Upvotes: 1

Akyegane
Akyegane

Reputation: 905

To correct this error:

  1. Create a view that is based on the desired table and that does not include the unsupported data type. 2.Drag the view from Server Explorer/Database Explorer onto the designer.

or see this article The Missing Linq to SQL Spatial,This article provides hints and hacks on how to use the SQL Server spatial data types -Geography and Geometry- in Linq to SQL

Upvotes: 3

Pranay Rana
Pranay Rana

Reputation: 176956

Check below article / answer for the detail :

SqlGeography and Linq to Sql

Is it possible to use SqlGeography with Linq to Sql?

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[].

Upvotes: 1

Related Questions