DB ConnectionState = Open, but context.SaveChanges throws a connection exception

Question

In my service, I have a background thread that best saves the thread stream of an object of a certain type. The code looks something like this:

        while (AllowRun)
        {
            try
            {
                using (DbContext context = GetNewDbContext())
                {
                    while (AllowRun && context.GetConnection().State == ConnectionState.Open)
                    {
                        TEntity entity = null;
                        try
                        {
                            while (pendingLogs.Count > 0)
                            {
                                lock (pendingLogs)
                                {
                                    entity = null;
                                    if (pendingLogs.Count > 0)
                                    {
                                        entity = pendingLogs[0];
                                        pendingLogs.RemoveAt(0);
                                    }
                                }

                                if (entity != null)
                                {
                                    context.Entities.Add(entity);
                                }
                            }
                            context.SaveChanges();
                        }
                        catch (Exception e)
                        {
                            // (1)
                            // Log exception and continue execution
                        }
                    }
                }
            }
            catch (Exception e)
            {
               // Log context initialization failure and continue execution
            }
        }

(this is basically the actual code, I skipped a few irrelevant parts that try to store pop-up objects in memory until we can save the material in the database again when the exception hits the block (1))

, , , Db. , - , . , ( , ), context.SaveChanges() ( (1)):

System.Data.EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details. ---> 
System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.

, context.GetConnection().State == ConnectionState.Open, true. , , , , . ( AllowRun ). , , , ?

, , "" ? , EntityException , reset , InnerException InvalidOperationException , , reset . , , ConnectionState , , DB. , , ?

Answer 1

?

, .

while (pendingLogs.Count > 0)
{
     lock (pendingLogs)
     {
          entity = null;
          if (pendingLogs.Count > 0)
          {
             entity = pendingLogs[0];
             pendingLogs.RemoveAt(0);
          }
     }

     if (entity != null)
     {
          context.Entities.Add(entity);
     }
}
context.SaveChanges();

Answer 2

.

Pending, , , dbConnection.

ANTS RedGate dbConnections, script StackOverflow: SQL Server?

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

;

, , GC , :

while (AllowRun)
    {
        try
        {
            while (pendingLogs.Count > 0)
            {
                using (DbContext context = GetNewDbContext())
                {
                    while (AllowRun && context.GetConnection().State == ConnectionState.Open)
                    {
                        TEntity entity = null;
                        try
                        {

                            lock (pendingLogs)
                            {
                                entity = null;
                                if (pendingLogs.Count > 0)
                                {
                                    entity = pendingLogs[0];
                                    pendingLogs.RemoveAt(0);
                                }
                            }

                            if (entity != null)
                            {
                                context.Entities.Add(entity);
                                context.SaveChanges();
                            }
                        }                        
                        catch (Exception e)
                        {
                            // (1)
                            // Log exception and continue execution
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
           // Log context initialization failure and continue execution
        }
    }

Answer 3

I recommend going below the URL: Timeout Expires normally when the sql query takes too long to start.

How does SQL job backup work? This could be a table lock or service restart.

ADONET Asynchronous Execution - Corrupted Connection Error