How do I use SqlFunctions.Checksum in C# to generate hashed string

Question

I am looking for replica of CHECKSUM MS SQL function in c#. When I fetch data in IDataReader, I want to convert one column from plain sting to hashed string using SqlFunctions.Checksum method.

Sample code:

IDataReader reader = base.DataBase.ExecuteReader(cmd);
var dsList = new List<IHumanReadableData>();
dsList = _humanReadableDataObjectMapper.MapList(reader);

dsList.ForEach(r => { 
         r.PersonName = SqlFunctions.Checksum(r.PersonName).ToString(); 
                    });

This snippet getting an error: "this function can only be invoked from LINQ to Entities".

Note:

• I don't want to use standard hash algorithms coz I don't want a long hash string to be generated.

• I want a hashed but relatively smaller string so that my users can compare them without I being exposing them sensitive data (i.e. PersonName).

• I don't want to perform this operation from MS SQL using CHECKSUM function.

Any ideas ??

Answer 1

You've got this error by calling .ForEach() method. This is a method of Linq-To-Object. But SqlFunctions are Linq-To-Entities methods. So you've got this error.

What you can do is create an extension method calculate hash like CheckSum of T-SQL(please, see here C# implementation of CheckSum()) and use it in your C# code. Let me show an example:

At first, create an extension method for string type:

public static class Extension
{
    public static int Checksum(this string text)
    {
        if (text == null)
            return 0;

        long sum = 0;
        byte overflow;            
        for (int i = 0; i < text.Length; i++)
        {
            sum = (long)((16 * sum) ^ Convert.ToUInt32(text[i]));
            overflow = (byte)(sum / 4294967296);
            sum = sum - overflow * 4294967296;
            sum = sum ^ overflow;
        }

        if (sum > 2147483647)
            sum = sum - 4294967296;
        else if (sum >= 32768 && sum <= 65535)
            sum = sum - 65536;
        else if (sum >= 128 && sum <= 255)
            sum = sum - 256;

        return (int)sum;
    }
}

and use it in your method:

static void Main(string[] args)
{
    List<Person> persons = new List<Person>()
    {
        new Person(){IdPerson = 1, FirstName = "1" },
        new Person(){IdPerson = 2, FirstName = "2" },
        new Person(){IdPerson = 3, FirstName = "3" },
        new Person(){IdPerson = 4},
    };

    persons.ForEach(r => {
        r.FirstName = r.FirstName.Checksum().ToString();
    });
}

UPDATE:

MSDN does not say what algorithm is used in CHECKSUM function. So you have to use LINQ-to-Entities to use T-SQL functions.