Comparing two byte arrays in SQL Server

Question

I have byte[] stored in a SQL Server database as varbinary(64) and I need to find a specific row with input byte[]. I'm using Entity Framework to access the database, thus I can run a linq query like this:

private MyTable GetMyTable(byte[] inputArray) 
{
   return _context.MyTable.FirstOrDefault(x => x.Hash.SequenceEqual(inputArray));
}

The issue is that SequenceEqual can't be translated into SQL query and the query will be evaluated locally (which is is undesirable behavior). Is it possible to replace SequenceEqual for simple compare operator ==? Or is this approach error-prone? Is there any better alternatives than this?

private MyTable GetMyTable(byte[] inputArray) 
{
   return _context.MyTable.FirstOrDefault(x => x.Hash == inputArray);
}

Note that my byte arrays will range from 32 to 64 bytes so it's not going to be huge array comparisons.

Actually, using Array.Equals seem much more "right" in this case than ==. Thank you so much @jdweng — Bukk94
– Bukk94, Commented Aug 13, 2020 at 15:08

David Browne - Microsoft · Accepted Answer · 2020-08-13 15:50:52Z

1

Is it possible to replace SequenceEqual for simple compare operator ==?

Probably yes, that will be tranlated to SQL as

  SELECT ...
  FROM [MyTable] AS [c]
  WHERE [c].[Hash] = @__inputArray_0

And = in SQL Server for binary/varbinary performs a byte-wise binary comparison on the values, ignoring trailing 0's. So if that's the comparison you want, then your're good to go.

edited Aug 13, 2020 at 15:50

answered Aug 13, 2020 at 13:30

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bukk94 Over a year ago

I tested both methods, == and Array.Equals as @jdweng suggested and they seems that both transforms to this. I just wanted to be sure. Thanks for your help!

Collectives™ on Stack Overflow

Comparing two byte arrays in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related