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 ??

StepUp · Accepted Answer · 2018-12-18 09:42:46Z

0

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.

edited Dec 18, 2018 at 9:42

answered Dec 18, 2018 at 9:08

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

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

1 Comment

Suresh Raut Over a year ago

I went through the provided link but it doesn't explain SqlFunctions.Checksum implementation. It has explained how to create hash using BINARY_CHECKSUM but I am not sure how reliable it will be as I tried hashing a string in MSSQL using BINARY_CHECKSUM function and same string I hashed in c# using the code provided in the link.. the output were completely different.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related