SQL connection timeout error with Entity Framework

user_1896787 0
Viewed 495

I am updating the 2600 records in a table at once with entity framework.

It was working previously but now suddenly started throwing the timeout error every time.

The timeout property is set to 150.

Also, multiple users are using the application at the same time.

Below is the code:

    foreach (var k in context.Keywords.Where(k => k.CurrentDailyCount > 0))
            {
               k.CurrentDailyCount = 1;
            }

            context.SaveChanges();

This is the error I'm facing:

enter image description here

enter image description here

What can be the issue behind this error? It was working fine but suddenly started throwing the timeout error.

asp.net-core entity-framework sql .net
2 Answers
var entries = context.Keywords.Where(k => k.CurrentDailyCount > 0) ?? new List<Keyword>(); 
foreach (var k in entries)
{
   k.CurrentDailyCount = 1;
}

await context.SaveChangesAsync();
  1. Store the filtered keywords in a variable to save time it takes to search: context.Keywords.Where(k => k.CurrentDailyCount > 0)
  2. Ensure filtered keywords is never null: ?? new List<Keyword>().
  3. Save records Asynchronously: await context.SaveChangesAsync();
user_35630130

At first, you may consider to select only primaryKey field and CurrentDailyCount. you can make it like 

context.Keywords.Select(x => new Keyword(){
   PrimaryKeyColumn = x.primaryKeyColumn,
   CurrentDailyCount = x.currentDailyCount 
}).Where(k => k.CurrentDailyCount > 0)

And also you should check the execution time of your sql statement. If CurrentDailyCount column is not indexed, it is no surprise that your Code gets timeout error.

The timeout property is set to 150.

Which timeout are you addressing? is it SQL Connection Timeout or Kestrel Server timeout? If you set SQL timeout period to 150 and the kestrel timeout value is default (Which is 120s) your code interrupts when it reaches to 120 seconds.

user_11189780
Related