是否有可能使用SqlGeography与LINQ to SQL的？ [英] Is it possible to use SqlGeography with Linq to Sql?

问题描述

我一直在尝试使用 Microsoft.SqlServer.Types.SqlGeography 不少问题。我知道，这凌SQL支持满井不是很大。我试过很多方法，什么会以预期的方式（数据库类型地理，CLR类型 SqlGeography 的开始）。这将产生引发NotSupportedException ，这是通过博客广泛讨论。

I've been having quite a few problems trying to use Microsoft.SqlServer.Types.SqlGeography. I know full well that support for this in Ling to Sql is not great. I've tried numerous ways, beginning with what would the expected way (Database type of geography, CLR type of SqlGeography). This produces the NotSupportedException, which is widely discussed via blogs.

然后，我已经走了下来治疗地理列作为 VARBINARY（最大），由于路径地理存储以二进制UDT。这似乎很好地工作（与一些二进制读写扩展方法）。

I've then gone down the path of treating the geography column as a varbinary(max), as geography is a UDT stored as binary. This seems to work fine (with some binary reading and writing extension methods).

不过，我现在正在运行到一个相当晦涩的问题，这似乎并没有发生很多其他人。

However, I'm now running into a rather obscure issue, which does not seem to have happened to many other people.

System.InvalidCastException：无法投型Microsoft.SqlServer.Types.SqlGeography'的对象类型System.Byte []

System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Byte[]'.

此错误是从 ObjectMaterializer 通过迭代查询时抛出。看来当包含在查询包含地理列的表隐式只发生（即使用的EntityRef＆LT;＆GT; 属性做连接）。

This error is thrown from an ObjectMaterializer when iterating through a query. It seems to only occur when the tables containing geography columns are included in a query implicitly (ie. using the EntityRef<> properties to do joins).

System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext（）

System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()

我的问题：如果我检索地理列 VARBINARY（最大），我可能会预测相反的错误：无法施展字节[] 到 SqlGeography 。那我会理解的。这我不知道。我确实有部分的LINQ to隐藏二进制转换SQL类的一些properies ......这些可能是问题？

My question: If I'm retrieving the geography column as varbinary(max), I might expect the reverse error: can't cast byte[] to SqlGeography. That I would understand. This I don't. I do have some properies on the partial LINQ to SQL classes that hide the binary conversion... could those be the issue?

任何帮助AP preciated，我知道有可能是没有足够的信息。

Any help appreciated, and I know there's probably not enough information.

附加功能：

• A 地理在服务器数据类型'= 地理在Visual Studio DBML设计师列生成此错误： 指定的类型'地理'是不是有效的提供程序类型。


• A 地理在没有服务器数据类型在Visual Studio DBML设计师列生成此错误：无法格式化节点值为作为执行SQL。

• A geography column in the Visual Studio dbml Designer with 'Server Data Type' = geography generates this error: The specified type 'geography' is not a valid provider type.
• A geography column in the Visual Studio dbml Designer with no 'Server Data Type' generates this error: Could not format node 'Value' for execution as SQL.

推荐答案

空间类型不被支持的LINQ到SQL。支持不是不是很大 - 这是不存在的。

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

您的可以的阅读为BLOB，但你不能这样做，只需在LINQ的更改列类型为SQL。您需要在数据库级别来改变你的查询返回列作为 VARBINARY ，使用 CAST 语句。您可以通过添加一个计算 VARBINARY 列，它的LINQ会高兴地映射到一个字节[] 。

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

在换句话说，一些DDL是这样的：

In other words, some DDL like this:

ALTER TABLE FooTable
ADD LocationData AS CAST(Location AS varbinary(max))

然后，从您的LINQ删除位置列SQL类，并使用 LocationData 代替。

Then, remove the Location column from your Linq to SQL class, and use LocationData instead.

如果您再需要访问的实际 SqlGeography 例如，你需要将其转换为从字节数组，使用的STGeomFromWKB 并的STAsBinary 。

If you then need access to the actual SqlGeography instance, you'll need to convert it to and from the byte array, using STGeomFromWKB and STAsBinary.

您可以通过局部的LINQ延伸到SQL实体类和添加自动转换特性使得这个过程多一点自动

You can make this process a bit more "automatic" by extending the partial Linq to SQL entity class and adding an auto-converting property:

public partial class Foo
{
    public SqlGeography Location
    {
        get { return SqlGeography.STGeomFromWKB(LocationData, 4326); }
        set { LocationData = value.STAsBinary(); }
    }
}

这假定 LocationData 是计算 VARBINARY 列的名称;不包括真正的位置在你的LINQ to SQL定义列，你在上面的特设的方式加入。

This assumes that LocationData is the name of the computed varbinary column; you don't include the "real" Location column in your Linq to SQL definition, you add it in the ad-hoc fashion above.

还要注意的是，你将不能够做很多与此列比读取和写入它的其他;如果您尝试实际上它查询（即包括在一个其中， predicate），那么你只会得到一个类似引发NotSupportedException 。

Note also that you won't be able to do much with this column other than read and write to it; if you try to actually query on it (i.e. including it in a Where predicate) then you'll just get a similar NotSupportedException.

这篇关于是否有可能使用SqlGeography与LINQ to SQL的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！