Linq&不支持的数据类型(地理位置) [英] Linq & unsupported data types (Geography)

问题描述

因此，Linq不支持Geography数据类型，该数据类型在可爱的将表拖放到Linq设计图"开发模型的工作中投入了主要的扳手.

有什么方法可以扩展Linq以使用Geography数据类型? 还是在需要使用地理位置"列时需要构建一个全新的数据层和一组查询?

我已经坚持了几天，如果可能的话就无法解决.

解决方案

将该列转换为varbinary(max)，Linq to SQL 可以处理该变量.避免在每个查询中执行此操作的一种方法是仅添加定义为CAST(GeographyColumn AS varbinary(max))的计算列.

一旦有了byte[]数据，就可以编写一个简短的实用程序方法，以使用MemoryStream和IBinarySerialize.Read/Write方法将其转换为实际的Microsoft.SqlServer.Types.SqlGeography类. /p>

据我所知，如果您需要使用任何CLR类型(包括地理位置，几何图形，hierarchyid和任何自定义类型)，这是唯一可行的解​​决方案-Linq不本地"支持它们中的任何一个.编写所有样板代码有点麻烦，但是您可以使用一些扩展方法来简化它.

您将无法以这种方式针对该列进行查询；但是，您可以使用此处.您可以通过这种方式使用设计器.只需将SqlGeography包装器属性添加到部分类中，然后将STGeomFromWKB方法与SqlBytes类一起使用.但是，那仍然不能为您提供内联查询功能.

So, Linq does not support the Geography data type, which throws a major spanner in the works in the lovely 'drag table onto the Linq design surface' developemnt model.

Is there any way that I can extend Linq to work with the Geography datatype? Or will I need to build a whole new datalayer and set of queries for whenever I need to use Geography columns?

I've been stuck on this for a few days and can't work out if it's possible.

解决方案

Cast the column to a varbinary(max), which Linq to SQL can handle. One way to avoid doing this in every query is just to add a computed column defined as CAST(GeographyColumn AS varbinary(max)).

Once you have the byte[] data, you can write a short utility method to convert it to the actual Microsoft.SqlServer.Types.SqlGeography class using a MemoryStream and the IBinarySerialize.Read/Write methods.

As far as I know, this is the only working solution if you need to work with any CLR type, including geography, geometry, hierarchyid, and any custom types - Linq doesn't "natively" support any of them. It's a bit of a pain to write all the boilerplate code, but you can make it easier with a few extension methods.

You won't be able to query against the column this way; however, you can get what I would call halfway there using the Linq Dynamic Query Library. You won't have intellisense or some of the other Linq goodies, but it's still composable and therefore better than hand-crafting every query, and you can get strong-typed results.

Update: I found a slightly cleaner solution here. You can use the designer this way; just add the SqlGeography wrapper property to a partial class and use the STGeomFromWKB method with the SqlBytes class. That still won't give you inline query capabilities, though.

这篇关于Linq&不支持的数据类型(地理位置)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！