LINQ fastest way to update lots of records (>2m)

Question

I have this loop:

using(var db = new MainContext())
{
    var q = db.tblInternalURLs;
    foreach (var rec in q)
    {
        db.ExecuteCommand("UPDATE tblInternalURLS SET hash = '" + LoginAPI.GetSha1(rec.URL) + "' WHERE ID = " + rec.ID);
    }
}

Converting the update query to db.ExecuteCommand has improved speed considerably, however I was wondering if there's a faster way to execute these queries as it still takes a long time over 2,000,000+ records. I believe a lot of the overhead is in the initial LINQ query. Is this correct?

Answer 1

Well, seeing as SQL Server supports hashing, you could avoid taking any data to the client by writing a SQL query to do the whole table in one go:

update 
 tblInternalURLS 
SET 
 hash = HASHBYTES('SHA1',CONVERT(nvarchar(4000), URL))

If the hash is stored as a string, sys.fn_varbintohexsubstring might be handy.