LINQ to SQL Designer and Geography Data Type

Question

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

One or more of the selected items contains a data type that is not supported by the constructor

I would be right to assume that this is the type of geography used in the table that causes this error?

Any pointers are greatly appreciated.

Answer 1

Check article / answer for detail:

SqlGeography and Linq to Sql

Can I use SqlGeography with Linq to Sql?

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

You can read them as a BLOB, but you cannot do this by simply changing the column type in Linq to SQL. You need to modify your queries at the database level to return the column as varbinary using the CAST statement. You can do this at the table level by adding a computed varbinary column, which Linq happily maps to byte [].

Answer 2

To fix this error:

• Create a view based on the desired table and which does not include an unsupported data type. 2. Remove the view from the server explorer / database explorer to the designer.

or see this article Missed Linq to SQL Spatial , This article provides tips and hacks on how to use SQL Server spatial data types —Geography and geometry — in Linq to SQL

Answer 3

I have a project (small, provided) that has grown to have new requirements for spatial types. I decided to see how easy it would be to migrate to the entity infrastructure from LINQ to SQL.

It took me no more than 30 minutes. Before you begin, make sure you have a backup copy of the source code.

• Install-Package EntityFramework -Version {pick one}
• Delete old .dbml file (s)
• Create a new entity model and update the references to the objects of the new objects (this is easier if you already have a basic repository or something related to LINQ to SQL). I went along this route (first / database constructor) because it was an easier upgrade path from LINQ to SQL. You can try the code first if you want, but ... YMMV.
• Update connection strings. The Entity Framework has its own weird wrapper around traditional SQL joins. My like this, yours may look different: metadata=res://*/;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS; Initial Catalog=MyDataBase; Integrated Security=True' metadata=res://*/;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS; Initial Catalog=MyDataBase; Integrated Security=True'
• Fix compilation errors. Most of them will be different from Insert/DeleteOnSubmit and EF Add/Remove or Add/RemoveRange . Transactional areas can work differently (consider using context.Database.BeginTransaction instead of transactional areas).
• Recover it after fixing compilation errors.

What is it. It was much simpler than I expected, and now I can use spatial types without any deception.