LinQ to SQL和CLR用户定义类型

I have created a User Defined Type in .Net 3.5 as per my blog entry at :

我根据我的博客条目在.Net 3.5中创建了一个用户定义类型:

http://jwsadlerdesign.blogspot.com/2009/04/this-is-how-you-register.html

This works fine when using SQL with technologies like nHibernate.

当使用SQL与nHibernate等技术时,这种方法很有效。

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.

但是,当我尝试将我的LinQ映射到SQL类以使用此UDT(属性定义而不是XML)时,我将该属性设置为枚举。我无法让LinQ映射到这种类型。我尝试过Image,Binary,varchar和integer,所有这些似乎都会发出Invalid Cast错误。

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.

特别是我得到错误'无法将类型的对象'ISTD.InstallManager.Common.Classes.SQLUDTTargetType'强制转换为'System.Byte []',任何想法或帮助将不胜感激。

James.

1 个解决方案

#1

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 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:

无论你如何声明属性,Linq to SQL仍然会拒绝给你UDT字段。所以你必须给它一个二进制字段。您不需要存储过程或任何自定义SQL,只需向表中添加计算列:

ALTER TABLE MyTable
ADD UDTField_Data AS CAST(UDTField AS varbinary(len))

Where len is whatever your UDT defines in the MaxByteSize attribute.

其中len是UDT在MaxByteSize属性中定义的任何内容。

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:

现在您终于可以访问列数据了。您可能想要使用UDT作为新属性的返回类型,认为Linq to SQL将找到您的转换运算符并自动从字节数组转换;不要打扰。 Linq to SQL将决定它实际上是一个序列化的.NET对象并且发出一条消息,其结果是“输入流不是有效的二进制格式”。相反,您需要另一层间接:

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:

一些注意事项,以明确这里发生了什么:

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.

实际的字段数据(udtField)被声明为UDT本身,而不是字节数组。原因是我们只希望在从数据库加载或保存到数据库时进行转换。如果每次访问它都必须将字节数组转换为UDT,这不仅会损害性能,而且如果UDT声明任何可变字段,也会导致不一致。

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.

raw byte []属性(UdtFieldData)被声明为private,因此使用者只能看到UDT本身。只要具有[Column]属性,Linq to SQL仍会读取它。

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.

UdtFieldData属性不声明存储属性。这很关键;如果您尝试使用UDT字段作为存储属性,您将获得相同的类型转换错误。

Finally, the UdtProperty property is how consumers actually get to access the data. To them it looks like any other property.

最后,UdtProperty属性是消费者实际访问数据的方式。对他们来说,它看起来像任何其他财产。

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表面设计师进行这种按摩可能会有困难,这只是我不使用它的几个原因之一;最好自己编写类,并在必要时使用SqlMetal帮助您。

#1