What is better way to update SQL Server database using Linq?

Question

I'm using SQL Server 2012 and Linq-to-SQL.

I have this method to update one row in the database after checking some conditions.

I wrote it in two ways. Which one is faster and less resource intensive? (SQL query, CPU count...)

Query #1:

In this one I used IEnumerable

internal static bool CheckSecretCodeLoginkError(String License, String SecretCode, DataClasses1DataContext db)
{
    IEnumerable<User> user = db.Users.Where(a => a.Licensekey == License).Select(a => a);

    if (SecretCode != user.First().SecretCode && !user.First().SkipSecretCode)
    {
        if (user.First().LastSecretChangeDate > DateTime.UtcNow - TimeToPreventSecretCodeChange)
        {
            //secret error
            return true;
        }
        else
        {
            //no error
            //update secret code and last change
            user.First().LastSecretChangeDate = DateTime.UtcNow;
            user.First().SecretCode = SecretCode;
            db.SubmitChanges();

            return false;
        }
    }
    else
    {
        return false;
    }
}

Query #2:

internal static bool CheckSecretCodeLoginkError(String License, String SecretCode, DataClasses1DataContext db)
{
    User user = db.Users.Where(a => a.Licensekey == License).Single();

    if (SecretCode != user.SecretCode && !user.SkipSecretCode)
    {
        if (user.LastSecretChangeDate > DateTime.UtcNow - TimeToPreventSecretCodeChange)
        {
            //secret error
            return true;
        }
        else
        {
            //no error
            //update secret code and last change
            db.Users.Where(a => a.Licensekey == License).Select(a => a).First().LastSecretChangeDate = DateTime.UtcNow;
            db.Users.Where(a => a.Licensekey == License).Select(a => a).First().SecretCode = SecretCode;

            db.SubmitChanges();

            return false;
        }
    }
    else
    {
        return false;
    }
}

I'll delete my answer and leave a comment here: You can see for yourself the SQL generated by Linq to SQL stackoverflow.com/a/17092821/397817 — Stephen Kennedy
– Stephen Kennedy, Commented Oct 26, 2014 at 13:41

marc_s · Accepted Answer · 2014-10-26 13:34:23Z

I would use query #2 - with an adaption:

internal static bool CheckSecretCodeLoginkError(String License, String SecretCode, DataClasses1DataContext db)
{
    // you store a reference in "user" here.....
    User user = db.Users.Where(a => a.Licensekey == License).Single();

    if (SecretCode != user.SecretCode && !user.SkipSecretCode)
    {
        if (user.LastSecretChangeDate > DateTime.UtcNow - TimeToPreventSecretCodeChange)
        {
            //secret error
            return true;
        }
        else
        {
            //no error
            //update secret code and last change

            // **REUSE** that reference you stored above!! 
            // Don't call .Where(...).Select(....).First() again!
            user.LastSecretChangeDate = DateTime.UtcNow;
            user.SecretCode = SecretCode;

            db.SubmitChanges();

            return false;
        }
    }
    else
    {
        return false;
    }
}

You're storing the single user you found at the beginning of the method in user - why are you calling the whole messy LINQ expression again when updating?!?! Just use that reference user that you already have!

Collectives™ on Stack Overflow

What is better way to update SQL Server database using Linq?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related