Binary(16)字段截断了Guid的尾随零 [英] Binary(16) field Is truncating Guid's trailing zeros

问题描述

在C#中，我有一个 byte [] 字段，称为UniqueId.我将此字段存储为SQL Server数据库(EF6)中的Binary(16).

In C#, I have a byte[] field called UniqueId. I store this field as a Binary(16) in a SQL Server database (EF6).

我注意到有时候，存储的guid是15字节而不是16字节.从数据库检索值后执行 var guid = new Guid(uniqueId)时，这将导致以下异常:

I have noticed that sometimes, the stored guid is 15 bytes instead of 16 bytes. This causes the following exception when I do var guid = new Guid(uniqueId) after retrieving the value from the db:

System.ArgumentException:GUID的字节数组必须正好为16长字节.

System.ArgumentException : Byte array for GUID must be exactly 16 bytes long.

在对此进行调查之后，我注意到，只要我生成的guid在十六进制的末尾包含"00"，SQL就会将其截断！

After investigating this, I noticed that whenever my generated guid contains "00" at the end of the hex, SQL truncates it!

示例:

生成的Guid(通过Guid.NewGuid): FF96F954777E8941A04774CD157C5C00(16字节)

Generated Guid (via Guid.NewGuid): FF96F954777E8941A04774CD157C5C00 (16 bytes)

在SQL Server中存储二进制(16):0xFF96F954777E8941A04774CD157C5C(15字节)

Stored Binary(16) in SQL Server: 0xFF96F954777E8941A04774CD157C5C (15 bytes)

如果您注意到，末尾的 00 将被截断.结果，如果我查询此字段并尝试将其字节转换为Guid，则会得到 System.ArgumentException .

If you notice, the 00 at the end is truncated. As a result, if I query this field and try to convert its bytes to a Guid, I'll get a System.ArgumentException.

还有其他人遇到这个问题吗?解决方法是什么?我正在考虑 Guid 的包装器，该包装器将继续生成guid，直到它没有尾随零为止，但这似乎很麻烦.

Is anybody else experiencing this problem? What is the workaround? I am thinking about a wrapper for Guid that keeps generating guids until it doesn't have trailing zeros, but that seems hacky.

更新1:由于你们的要求，我运行了SQL事件探查器，这是由EF(节略版)生成的SQL:

Update 1: Since you guys requested it, I ran the SQL profiler, and this is the SQL generated by EF (abridged version):

exec sp_executesql N'INSERT [dbo].[customers]([UniqueId])
VALUES (@0)',N'@0 varbinary(max)',@0=0xFF96F954777E8941A04774CD157C5C00

推荐答案

如果列的 ANSI_PADDING 设置为 BINARY(16)中看到此行为> OFF ，并且该列允许 NULL (演示).

You see this behaviour with BINARY(16) if the ANSI_PADDING setting for the column is OFF and the column allows NULL (demo).

修复该问题最好是更改表定义，以便该列使用 uniqueidentifier 数据类型或至少启用 ANSI_PADDING .

Fixing it would ideally involve changing the table definition so the column is using uniqueidentifier datatype or at least has ANSI_PADDING on.

要更改 ANSI_PADDING 的语义，将涉及 SET ANSI_PADDING ON ，然后添加一个新的 binary(16)列-从旧列填充它然后拖放并重命名.这是您的应用程序可以忍受列的可能重新排序，如果它不能应对这种重新排序并且 UniqueId 不是表中的最后一列，则需要创建一个新表并进行迁移所有数据.

To change the ANSI_PADDING semantics will involve SET ANSI_PADDING ON then adding a new binary(16) column - populating it from the old column and then a drop and rename. And this is if your application can tolerate a possible reordering of columns, if it can't cope with this reordering and UniqueId is not the last column in the table you will need to create a new table and migrate all data across.

这篇关于Binary(16)字段截断了Guid的尾随零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！