CODEWITHSUNDEEP
c#sql-server-2008c#-4.0linq-to-entities
Luke101
Luke101

Reputation: 65298

Linq: How to store MD5 hash column in database

I need to put an index on a md5 hash column in the database. I will perform searches on the md5 column. I was going to store the hash as a CHAR(32) but I seen the binary column option as well. Will storing a md5 hash work better in a binary column or a char(32). Can i use Linq to Entities to query a binary column? If so, How would I go about this?

Upvotes: 2

Views: 3144

Answers (3)

Salvatore Previti
Salvatore Previti

Reputation: 9080

If you are using SQLServer or any other server that supports 128 bit GUID types... you can use the GUID type to express an MD5 value.

Since MD5 is 16 byte (128 bit) you can easily convert it to a GUID. For doing that in C# you can use the Guid structure and\or write simple conversion routines by hand.

Guids are in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx where x is an hexadecimal character, but are internally stored as 128 bit integers, so, they occupy very little amount of space and are very, very fast for queries!

GUIDS works much better than char or binary, they are fixed size and are often used as keys\indices instead of INT when more bits are needed due to their very high speed and low space consumption.

Upvotes: 5

KeithS
KeithS

Reputation: 71591

It really depends on how you represent your hash in code. If it's a byte array, go ahead and use the binary DB type. If it's a string, use that. Either way, it's all binary data at some level, it's just how the computer is told to interpret it when showing the data to you.

Upvotes: 0

James Fuqua
James Fuqua

Reputation: 1

If the indexing is the same it won't matter which type you select, the difference is going to be in the storage. The binary type will likely be smaller, whereas the char type will encode the values as integers. Really at the end of the day I'd use the char because it will be more forgiving then the binary. So unless you're storing a ton of these, millions of them, it's not going to make a big difference.

As for the LINQ, I'm not sure, but I'm pretty sure you can, it'll just be field instead of field. That's the other reason I'd go char, it makes dealing with linq easier.

Upvotes: 0

Related Questions