Problem calling stored procedure with a fixed length binary parameter using Entity Framework

Question

I have a problem calling stored procedures with a fixed length binary parameter using Entity Framework. The stored procedure ends up being called with 8000 bytes of data no matter what size byte array I use to call the function import. To give some example, this is the code I am using.

byte[] cookie = new byte[32];
byte[] data = new byte[2];
entities.Insert("param1", "param2", cookie, data);

The parameters are nvarchar(50), nvarchar(50), binary(32), varbinary(2000)

When I run the code through SQL profiler, I get this result.

exec [dbo].[Insert] @param1=N'param1',@param2=N'param2',@cookie=0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

[SNIP because of 16000 zeros]

,@data=0x0000

All parameters went through ok other than the binary(32) cookie. The varbinary(2000) seemed to work fine and the correct length was maintained.

Is there a way to prevent the extra data being sent to SQL server? This seems like a big waste of network resource.

Answer 1

EF 4 always uses largish params, because using param-size = data-size mostly means that the query can't be re-used with new param values, so SQL can't cache the queries for you. In other words, what it's doing now is probably more efficient overall than using a smaller param, if your data size ever changes.

If your data size never changes, then use a different column width.