LinQ to SQL和CLR用户定义的类型 [英] LinQ to SQL and CLR User Defined Types
问题描述
我已经在.Net 3.5中根据我的博客条目创建了用户定义类型：
I have created a User Defined Type in .Net 3.5 as per my blog entry at :
http://jwsadlerdesign.blogspot.com/2009/04/this-is-how-you-register.html
在将SQL与nHibernate之类的技术一起使用时，此方法很好用。
This works fine when using SQL with technologies like nHibernate.
但是，当我尝试将LinQ映射到SQL类以使用此UDT（属性定义不是XML）时，我将该属性设置为枚举。我无法让LinQ映射到这种类型。我尝试了Image，Binary，varchar和integer，它们似乎都发出了无效的Cast错误。
However, when I try to map my LinQ to SQL class to use this UDT (with attribute defintions not XML), and I setup the property as the enumeration. I cannot get LinQ to map to this type. I have tried Image, Binary, varchar and integer all of which seem to issue Invalid Cast errors.
特别是我得到错误无法转换类型为 ISTD.InstallManager.Common.Classes.SQLUDTTargetType的对象以类型为 System.Byte []的任何提示否则将不胜感激。
In particular I get the error 'Unable to cast object of type 'ISTD.InstallManager.Common.Classes.SQLUDTTargetType' to type 'System.Byte[]' any ideas or help would be much appreciated.
詹姆斯。
推荐答案
更新：我最近遇到了这个问题，发现以前的解决方案还不够完善。尽管所有文档都说了那样，但可以做到这一点，但是有些痛苦。
UPDATE: I ran into this myself recently and found that the previous solution wasn't quite complete. Despite what all of the documentation says, it is possible to do this, but somewhat painful.
第一步，为了您自己的方便，是要实现一些转换运算符：
The first step, for your own convenience, is to implement some conversion operators:
public class MyUDT : INullable, IBinarySerialize
{
// Class implementation would go here
// ...
public static explicit operator MyUDT(byte[] data)
{
using (MemoryStream stream = new MemoryStream(data))
{
using (BinaryReader reader = new BinaryReader(stream))
{
MyUDT result = new MyUDT();
result.Read(reader);
return result;
}
}
}
public static explicit operator byte[](MyUDT x)
{
using (MemoryStream ms = new MemoryStream())
{
using (BinaryWriter writer = new BinaryWriter(ms))
{
x.Write(writer);
}
return ms.ToArray();
}
}
}
Linq to SQL仍将保持不变-out拒绝给您UDT字段，无论您如何声明该属性。因此，您必须给它一个二进制字段。为此，您不需要存储过程或任何自定义SQL，只需将计算列添加到表中即可：
Linq to SQL will still flat-out refuse to give you the UDT field, no matter how you declare the property. So you have to give it a binary field instead. You don't need a stored procedure or any custom SQL for this, just add a computed column to your table:
ALTER TABLE MyTable
ADD UDTField_Data AS CAST(UDTField AS varbinary(len))
其中 len 是您的UDT在 MaxByteSize 属性中定义的。
Where len is whatever your UDT defines in the MaxByteSize attribute.
现在，您终于可以访问列数据了。您可能会想将UDT用作新属性的返回类型，以为Linq to SQL会找到您的转换运算符并自动从字节数组转换；不要打扰Linq to SQL会确定它实际上是序列化的.NET对象，并发出一条消息，表明输入流不是有效的二进制格式。相反，您需要另一层间接访问：
Now you can finally get access to the column data. You might be tempted to use your UDT as the return type of the new property, thinking that Linq to SQL will find your conversion operator and automatically convert from the byte array; don't bother. Linq to SQL will decide that it's actually a serialized .NET object and spit out a message to the effect of "input stream is not a valid binary format." Instead, you need another layer of indirection:
private MyUDT udtField;
[Column(Name = "UDTField_Data", DbType = "varbinary(len)")]
private byte[] UdtFieldData
{
get { return (byte[])udtField; }
set { udtField = (MyUDT)value; }
}
public MyUDT UdtProperty
{
get { return udtField; }
set { udtField = value; }
}
一些注意事项可以弄清楚这里发生了什么：
A few notes to make it clear what's going on here:
- 实际的字段数据（ udtField ）被声明为UDT本身，而不是字节数组。原因是我们只希望在从数据库加载或保存到数据库时进行转换。如果必须在每次访问字节数组时将其转换为UDT，不仅会影响性能，而且如果UDT声明任何可变字段，也会导致不一致。
- 原始数据byte []属性（ UdtFieldData ）被声明为私有，因此使用者仅看到UDT本身。只要具有[Column]属性，Linq to SQL仍会读取它。
- UdtFieldData 属性不声明存储属性。这很关键；如果尝试将UDT字段用作存储属性，则只会遇到相同的类型转换错误。
- 最后， UdtProperty 属性是使用者的使用方式实际上可以访问数据。对他们来说，它就像其他财产一样。
- The actual field data (udtField) is declared as the UDT itself, not a byte array. The reason for this is that we only want the conversion to happen when loading from or saving to the database. If you had to convert the byte array to the UDT every time you accessed it, it would not only hurt performance, but it would cause inconsistencies if the UDT declares any mutable fields.
- The raw byte[] property (UdtFieldData) is declared private, so consumers only see the UDT itself. Linq to SQL will still read it as long as it has the [Column] attribute.
- The UdtFieldData property does not declare a storage property. This is critical; if you try to use the UDT field as the storage property, you'll just get the same type conversion error.
- Finally, the UdtProperty property is how consumers actually get to access the data. To them it looks like any other property.
不幸的是，您必须跳很多圈才能使它起作用，但是它确实有效。通过Linq表面设计器进行此类按摩可能会遇到困难，这只是我不使用它的几个原因之一；最好自己编写类，并在必要时使用SqlMetal来帮助您。
It's unfortunate that you have to jump through so many hoops to get this to work, but it does work. You'll probably have difficulties doing this kind of massaging through the Linq surface designer, which is just one of several reasons why I don't use it; better to write the classes yourself and use SqlMetal to help you along if necessary.
这篇关于LinQ to SQL和CLR用户定义的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
