EF inserting values in table failed after some time

Question

I am working on EF. I am trying to insert into a table, the insert function is in a thread.

private void DataReceivedHandler(object sender, SerialDataReceivedEventArgs e)
{
    int bytes = port.BytesToRead;
    //string indata = sp.ReadExisting();

    Thread.Sleep(50);

    try
    {
        receivedBytes = port.BaseStream.Read(buffer, 0, (int)buffer.Length);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message.ToString());
    }

    var receiveData = BitConverter.ToString(buffer, 0, receivedBytes);
    var finalData = receiveData.Replace("-", "");
    //Thread.Sleep(100);

    Console.WriteLine("Thread Going to Start");

    new Thread(() => {
            SaveData(finalData);
        }).Start(); // starting the thread

    port.DiscardOutBuffer(); 
    port.DiscardInBuffer();
}

And this is my save data function

public void SaveData(string finalData)
{
    Console.WriteLine(LineNumber() + "Data Transmiting...");
    thread = Thread.CurrentThread;         


mdc_dbEntities e = new mdc_dbEntities();

            var msn = e.mdc_meter_config.Where(m => m.m_hex == sr).Select(s => new { s.msn, s.p_id, s.meter_id }).ToList();

    var H = finalData.Substring(0, 2);

    using (mdc_dbEntities u = new mdc_dbEntities())
    {
        foreach (var res in msn)
        {
            var cust_id = e.mdc_meter_cust_rel.Where(m => m.msn == res.msn)
                                              .Select(s => s.cust_id)
                                              .FirstOrDefault();

            mdc_meters_data data = new mdc_meters_data()
                    {
                        msn = res.msn,
                        cust_id = cust_id,
                        device_id = res.meter_id.ToString(),
                        kwh = e_val.ToString(),
                        voltage_p1 = a_vol_val.ToString(),
                        voltage_p2 = b_vol_val.ToString(),
                        voltage_p3 = c_vol_val.ToString(),
                        current_p1 = a_curr_val.ToString(),
                        current_p2 = b_curr_val.ToString(),
                        current_p3 = c_curr_val.ToString(),
                        data_date_time = Convert.ToDateTime(theDate.ToString(format)),
                        d_type = d_type.ToString(),
                        pf1 = a_pf_val.ToString(),
                        pf2 = b_pf_val.ToString(),
                        pf3 = c_pf_val.ToString(),
                        p_id = res.p_id,
                    };
            u.mdc_meters_data.Add(data);
        }

        u.SaveChanges();
    }

    Console.WriteLine(LineNumber() + "Data Saved");
    Thread.Sleep(50);
}

try
{
    thread.Abort(); // aborting it after insertion
    //Thread.Sleep(50);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message.ToString());
}
}

The above code runs for some time, but after that I encountered an error at u.SaveChanges();

System.Data.Entity.Core.EntityException: 'An error occurred while closing the provider connection. See the inner exception for details.'

MySqlException: Fatal error encountered during command execution.
MySqlException: Fatal error encountered attempting to read the resultset.
MySqlException: Reading from the stream has failed.

IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

I have looked into each solution and tried them but still unable to resolve this issue. I must be missing something that I don't know.

Update 1 My whole code

Calling constructor

  public CommunicationEngine()
    {
        port.ReadTimeout = 500;
        port.DataReceived += new SerialDataReceivedEventHandler(DataReceivedHandler);
        port.Open();

        Console.WriteLine("Port opened successfully");

        Console.WriteLine("I am Recieving");
    }

Calling handler

 private void DataReceivedHandler(object sender, SerialDataReceivedEventArgs e)
    {

        int bytes = port.BytesToRead;


        Thread.Sleep(50);
        Console.WriteLine("Bytes are ok..." + port.BytesToRead + " Recieved ");
        try
        {
            receivedBytes = port.BaseStream.Read(buffer, 0, (int)buffer.Length);

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message.ToString());
        }


        var receiveData = BitConverter.ToString(buffer, 0, receivedBytes);
        var finalData = receiveData.Replace("-", "");
        //Thread.Sleep(100);

        Console.WriteLine("Thread Going to Start");

        try
        {
            new Thread(() => {
                SaveData(finalData);
            }).Start();
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message.ToString());
        }          


        port.DiscardOutBuffer(); port.DiscardInBuffer();



    }

Saving data into DB

public void SaveData(string finalData)
    {

        Console.WriteLine(LineNumber() + "Data Transmiting...");
        thread = Thread.CurrentThread;

        if (finalData.Length == 138)
        {
            comm = true;

            var H = finalData.Substring(0, 2);

            var FC = finalData.Substring(2, 9);

            var len = finalData.Substring(10, 2);

            var sr = finalData.Substring(12, 12);

            var energy_tag = finalData.Substring(24, 4);

            var e_val = hexToDec(finalData.Substring(28, 8)) / 10;

            var a_curr_tag = finalData.Substring(36, 4);

            var a_curr_val = hexToDec(finalData.Substring(40, 8)) / 1000;

            var b_curr_tag = finalData.Substring(48, 4);

            var b_curr_val = hexToDec(finalData.Substring(52, 8)) / 1000;

            var c_curr_tag = finalData.Substring(60, 4);

            var c_curr_val = hexToDec(finalData.Substring(64, 8)) / 1000;

            var a_vol_tag = finalData.Substring(72, 4);

            var a_vol_val = hexToDec(finalData.Substring(76, 8)) / 10;

            var b_vol_tag = finalData.Substring(84, 4);

            var b_vol_val = hexToDec(finalData.Substring(88, 8)) / 10;

            var c_vol_tag = finalData.Substring(96, 4);

            var c_vol_val = hexToDec(finalData.Substring(100, 8)) / 10;

            var a_pf_tag = finalData.Substring(108, 4);

            var a_pf_val = hexToDec(finalData.Substring(112, 4)) / 1000;

            var b_pf_tag = finalData.Substring(116, 4);

            var b_pf_val = hexToDec(finalData.Substring(120, 4)) / 1000;

            var c_pf_tag = finalData.Substring(124, 4);

            var c_pf_val = hexToDec(finalData.Substring(128, 4)) / 1000;

            var crc = finalData.Substring(132, 4);

            var ftr = finalData.Substring(136, 2);


            var d_type = "600";

            DateTime theDate = DateTime.Now;

            string format = "yyyy-MM-dd HH:mm:ss";
            Console.WriteLine(LineNumber() + "Data Ready to be inserted in DB");
            using (mdc_dbEntities u = new mdc_dbEntities())
            {
                var msnList = u.mdc_meter_config.Where(m => m.m_hex == sr)
                .Select(s => new { s.msn, s.p_id, s.meter_id })
                .ToList();

                foreach (var res in msnList)
                {
                    var cust_id = u.mdc_meter_cust_rel.Where(m => m.msn == res.msn)
                        .Select(s => s.cust_id)
                        .FirstOrDefault();

                    mdc_meters_data data = new mdc_meters_data()
                    {
                        msn = res.msn,
                        cust_id = cust_id,
                        device_id = res.meter_id.ToString(),
                        kwh = e_val.ToString(),
                        voltage_p1 = a_vol_val.ToString(),
                        voltage_p2 = b_vol_val.ToString(),
                        voltage_p3 = c_vol_val.ToString(),
                        current_p1 = a_curr_val.ToString(),
                        current_p2 = b_curr_val.ToString(),
                        current_p3 = c_curr_val.ToString(),
                        data_date_time = Convert.ToDateTime(theDate.ToString(format)),
                        d_type = d_type.ToString(),
                        pf1 = a_pf_val.ToString(),
                        pf2 = b_pf_val.ToString(),
                        pf3 = c_pf_val.ToString(),
                        p_id = res.p_id,
                    };
                    u.mdc_meters_data.Add(data);

                }
                try
                {
                    u.SaveChanges();
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.Message.ToString());
                }

            }                

            Console.WriteLine(LineNumber() + "Data Saved");
            Thread.Sleep(50);

        }
        else if(finalData.Length == 30)
        {
            var msn_no = finalData.Substring(12, 12);

            mdc_dbEntities p = new mdc_dbEntities();

            var update = p.meter_control.Where(c => (c.comm_executed == 0))
                          .Where(o => (o.m_hex == msn_no))
                          .SingleOrDefault();

            if(update.comm_sent == "Disconnect")
            {
                update.comm_executed = 1;
                update.comm = 0;
                p.SaveChanges();
                Console.WriteLine("Meter Disconnected....");
            }
            else if(update.comm_sent == "Connect")
            {

                update.comm_executed = 1;
                update.comm = 1;
                p.SaveChanges();
                Console.WriteLine("Meter Connected....");
            }             

            comm = true;
        }
        else
        {
            comm = true;
        }

        try
        {
            thread.Abort();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message.ToString());
        }

    }

Any help would be highly appreciated.

Answer 1

Executing EF related changes in a manually initiated thread is not a good idea. Try to run the EF changes in the same thread. If you are bothered with processing incoming requests, use Async, and Await feature. I have modified your code to accommodate this feature. Please try this.

private void DataReceivedHandler(object sender, SerialDataReceivedEventArgs e)
{
    int bytes = port.BytesToRead;
    //string indata = sp.ReadExisting();

    try
    {
        receivedBytes = port.BaseStream.Read(buffer, 0, (int)buffer.Length);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message.ToString());
    }

    var receiveData = BitConverter.ToString(buffer, 0, receivedBytes);
    var finalData = receiveData.Replace("-", "");

    Console.WriteLine("Thread Going to Start");

    SaveDataAsync(finalData).Wait(); // this call will become sync and runs under main thread.

    port.DiscardOutBuffer(); 
    port.DiscardInBuffer();
}

public async Task<bool> SaveDataAsync(string finalData)
{
mdc_dbEntities e = new mdc_dbEntities();

            var msn = e.mdc_meter_config.Where(m => m.m_hex == sr).Select(s => new { s.msn, s.p_id, s.meter_id }).ToList();

    var H = finalData.Substring(0, 2);
var isSaveSuccess = false;
    using (mdc_dbEntities u = new mdc_dbEntities())
    {
        foreach (var res in msn)
        {
            var cust_id = e.mdc_meter_cust_rel.Where(m => m.msn == res.msn)
                                              .Select(s => s.cust_id)
                                              .FirstOrDefault();

            mdc_meters_data data = new mdc_meters_data()
                    {
                        msn = res.msn,
                        cust_id = cust_id,
                        device_id = res.meter_id.ToString(),
                        kwh = e_val.ToString(),
                        voltage_p1 = a_vol_val.ToString(),
                        voltage_p2 = b_vol_val.ToString(),
                        voltage_p3 = c_vol_val.ToString(),
                        current_p1 = a_curr_val.ToString(),
                        current_p2 = b_curr_val.ToString(),
                        current_p3 = c_curr_val.ToString(),
                        data_date_time = Convert.ToDateTime(theDate.ToString(format)),
                        d_type = d_type.ToString(),
                        pf1 = a_pf_val.ToString(),
                        pf2 = b_pf_val.ToString(),
                        pf3 = c_pf_val.ToString(),
                        p_id = res.p_id,
                    };
            u.mdc_meters_data.Add(data);
        }

       isSaveSuccess = (await u.SaveChangesAsync())>0; // if records inserted, the count will be more than 0
    }

   return isSaveSuccess;
}

}