locked
entity framework reconnect RRS feed

  • Question

  • Hi There,

    I have a difficulty in figuring out how to make my app to recover after network issues. I am using a basic concept - a button that allows user to reconnect to a database and to refresh controls with data from the database. The basic code is shown below. The problem I have encountered the memory leak started showing up, perhaps GC is not collecting right after implicit disposal?

    The major issue is that even the network is back the subsequent execution of this code fails either with unable to open or 'an error occurred while executing the command definition'. 

    What is the right way of reconnecting to a database?

    try
                {
                    using (Dept6081_JBaseEntities jbase = new Dept6081_JBaseEntities())
                    {
                        Debug.WriteLine("started connecting");
                        jbase.Database.Connection.ConnectionString = _SQLConnectionString + "MultipleActiveResultSets=true";
    
                        //get the last modified batches
                        var QueryBatches = (from ticket in jbase.CU_M_Tickets
                                            group ticket by ticket.SyteLine_Job into gp
                                            select new
                                            {
                                                gp.Key,
                                                ID = (from t in gp
                                                      orderby t.ID descending
                                                      select t.ID).FirstOrDefault()
                                            });
    
                        //oder them by entry
                        var orderBatches = from b in QueryBatches
                                           orderby b.ID descending
                                           select b;
    
    
                        var QueryAssembly = (from prog in jbase.CU_M_TestPrograms
                                             orderby prog.ProductRevision
                                             select prog.ProductRevision).Distinct();
    
    
                        var platforms = (from plat in jbase.CU_M_Stations
                                         where string.IsNullOrEmpty(plat.Station) == false
                                         orderby plat.Station
                                         select plat.Station).ToList().Distinct();
    
                        ControlData controlData = new ControlData();
    
                        controlData.Batches = (from batch in orderBatches
                                               where string.IsNullOrEmpty(batch.Key) == false
                                               select batch.Key).ToList();
    
                        controlData.Assemblies = (from assy in QueryAssembly
                                                  where string.IsNullOrEmpty(assy) == false
                                                  select assy).ToList();
                        controlData.Platforms = (from platform in platforms
                                                 where string.IsNullOrEmpty(platform) == false
                                                 select platform).ToList();
                        
                        controlData.Platforms.Insert(0, "ALL");
    
    
                        return controlData;
    
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                    Debug.WriteLine("connection error");
                    return null;
                }

    Tuesday, September 29, 2020 4:02 PM

All replies

  • Hello,

    No guarantees this will resolve your issue. 

    Try using AsNoTracking on each query to see if the ChangeTracker is holding entities in memory. Usually AsNoTracking is best used for over 1,000 records returned but here we are seeing if this will cause memory leakage to go away.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, September 29, 2020 4:37 PM
  • I have a difficulty in figuring out how to make my app to recover after network issues. I am using a basic concept - a button that allows user to reconnect to a database and to refresh controls with data from the database. 

    What kind of program is this? The statements lead me to believe that optimal program design is not being used. And it should never happen that the user has to press a button to help a program recover.

    Tuesday, September 29, 2020 5:08 PM