是否可以将 SqlGeography 与 Linq to Sql 一起使用? [英] Is it possible to use SqlGeography with Linq to Sql?

问题描述

我在尝试使用 Microsoft.SqlServer.Types.SqlGeography 时遇到了很多问题.我很清楚在 Linq to Sql 中对此的支持不是很好.我已经尝试了很多方法，从预期的方式开始(geography 的数据库类型，SqlGeography 的 CLR 类型).这会产生 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 Linq 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.

然后我将 geography 列视为 varbinary(max)，因为 geography 是存储的 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<> 属性进行连接).

This error is thrown from an ObjectMaterializer when iterating through a query. It seems to occur only 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()

我的问题:如果我将 geography 列检索为 varbinary(max)，我可能会期望反向错误:can't cast byte[] 到 SqlGeography.我会明白的.这个我不我确实有一些隐藏二进制转换的部分 LINQ to SQL 类的属性......这些可能是问题吗?

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?

感谢任何帮助，我知道可能没有足够的信息.

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

附加:

• Visual Studio dbml 设计器中带有服务器数据类型"=geography 的 geography 列生成此错误:指定的类型geography"不是有效的提供程序类型.
• Visual Studio dbml 设计器中没有服务器数据类型"的 geography 列会生成此错误:无法将节点值"格式化为 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 to SQL 不支持空间类型.支持不是不好" - 它不存在.

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

您可以将它们作为 BLOB 读取，但是您不能通过简单地将 Linq 中的列类型更改为 SQL 来做到这一点.您需要在数据库级别更改查询，以使用 CAST 语句将列作为 varbinary 返回.您可以通过添加计算的 varbinary 列在表级别执行此操作，Linq 会很乐意将其映射到 byte[].

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 to SQL 类中删除 Location 列，并使用 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 定义中包含真正的"Location 列，而是以上面的临时方式添加它.

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.

另请注意，除了读写此列之外，您将无法对它做太多事情；如果您尝试对其进行实际查询(即，将其包含在 Where 谓词中)，那么您只会得到类似的 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屋！