What's the best way to update a SQL Server database using Linq?
I am 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 this in two ways. Which one is faster and less resource intensive? (SQL query, CPU counter ...)
Request # 1:
In this 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;
}
}
Request # 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;
}
}
+3
source to share
1 answer
I would use query # 2 - with adaptation:
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 keep the only user you found at the beginning of the method in user
- why are you calling the whole messy LINQ expression again on update?!?! Just use this link user
you already have!
+1
source to share