Get<T> won't work if the key type is byte[]

[NZTSL]

Hi, So if I have this table:

CREATE TABLE dbo.CdrHash ( 
  Hash BINARY(16) NOT NULL  PRIMARY KEY NONCLUSTERED,
  StartDate DATETIME2 NOT NULL, 
  ) 

With the corresponding POCO:

    public class CdrHash
    {
        public byte[] HashId { get; set; }
        public DateTime StartDate { get; set; }
    }

Then this code will return null:

var cdrHash = cnx.Get<CdrHash>(cdr.HashId);

So I have to use this one instead:

var cdrHash = cnx.Query<CdrHash>("select * from CdrHash where HashId = @Id", new {Id = cdr.HashId}).Single();

Indeed the generated SQL code for the Get() looks a bit strange to me:

exec sp_executesql N'SELECT [CdrHash].[HashId], [CdrHash].[StartDate] FROM [CdrHash] WHERE ([CdrHash].[HashId] IN (@HashId_0, @HashId_1, @HashId_2, @HashId_3, @HashId_4, @HashId_5, @HashId_6, @HashId_7, @HashId_8, @HashId_9, @HashId_10, @HashId_11, @HashId_12, @HashId_13, @HashId_14, @HashId_15))',
N'@HashId_0 tinyint,@HashId_1 tinyint,@HashId_2 tinyint,@HashId_3 tinyint,@HashId_4 tinyint,@HashId_5 tinyint,@HashId_6 tinyint,@HashId_7 tinyint,@HashId_8 tinyint,@HashId_9 tinyint,@HashId_10 tinyint,@HashId_11 tinyint,@HashId_12 tinyint,@HashId_13 tinyint,@HashId_14 tinyint,@HashId_15 tinyint',
@HashId_0=2,@HashId_1=3,@HashId_2=237,@HashId_3=170,@HashId_4=71,@HashId_5=210,@HashId_6=43,@HashId_7=167,@HashId_8=142,@HashId_9=197,@HashId_10=218,@HashId_11=148,@HashId_12=109,@HashId_13=30,@HashId_14=196,@HashId_15=95

Regards, Nick.

[timdooling]

I would initially suggest that you make your byte array have a length of 16.

Although I know there is a reason you are using a hash as an id field, wouldn't life be a lot simpler if you used it as property and a plain-old-int as an id?

[NZTSL]

Hi, thanks for your answer.

Actually i'm using the column to store around 200 millions SHA-1 hash (20 bytes), so I can't store it in a int, and I think a string would be pretty slow to index, what do you think ?

[timdooling]

The range of int on sql server is: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) or 4 bytes storage size. The C# is int or System.Int32 and is the same size. So, I think int would work in your situation for a while. There are also bigint types, etc. for MSSS.

I would still suggest that you make the length of the array involved fixed.

Dapper Extensions is setup to work well with identity fields, and an int identity field would make life a lot easier.

Because your hash field ends in Id, DE automatically declares it a key field whether you want it to or not.

You can download the source code and examine it for what it does, and I would recommend doing that to see how the wrapper works in your particular situation.

I also don't think the code authors respond to issues any more, so good luck with that.

[tmsmith]

not sure if you still have this problem. The problem is that DapperExtension predicate sees the value as an array so it is translating it to an IN statement.

[valfrid-ly]

I don't think that we have support for this kind of field in all database supported by DapperExtension. As tmsmith told before as it is an array it goes to an in not a single value.

I'm looking into it but it'll probably require you to inform this on the map