8

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.

jocull
  • 20,008
  • 22
  • 105
  • 149
Chin
  • 12,582
  • 38
  • 102
  • 152

3 Answers3

3

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

ozba
  • 6,522
  • 4
  • 33
  • 40
Akyegane
  • 895
  • 6
  • 15
1

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

Community
  • 1
  • 1
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • Thanks, this project has just started. Would I be better to move to a different table mapping(orm) framework that supports the geography type? Are there any? – Chin Aug 27 '10 at 06:23
  • @Chin - check the article where some work around are there i hope that will work for you -- than no need to move on another orm – Pranay Rana Aug 27 '10 at 07:15
1

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.

jocull
  • 20,008
  • 22
  • 105
  • 149
  • Usually it's not that easy to switch to entity framework in an existing project. Anyway the question was tagged with ling-2-sql so answering to use entity framewok is not a solution. – Dave de Jong Aug 20 '18 at 10:42
  • 1
    The official answer is that LINQ-2-SQL doesn't support geo-types. If you need a proper solutions from Microsoft, I'm pretty sure this is it. – jocull Aug 20 '18 at 15:24