c# - Issues Using Foreach Loop

Question

I am trying to use Foreach loop to calculate the Interest paid by those that were issued loan in my application. The application loads the list of all those issued loan, those whose loan account balance is less than zero are debited for repayment. It runs fine if the person has received loan only once but if he receives twice or three times, the loan uses the same old balance for all the three instances.

Below is the code:

DateTime WATTime = Timezones.WATTimezone();  //Calling the Timezone method from class to use the West Central Africa Timezone 

        var date = DateTime.Today;
        var zone = TimeZoneInfo.FindSystemTimeZoneById("W. Central Africa Standard Time");

        // var presentDates = date.Date;  // das;
        DateTime currentTime = TimeZoneInfo.ConvertTime(date, zone);

        var loanDates = currentTime.Date;
        var loanDate = loanDates.ToShortDateString();


        List<LoanProcessed> loanProcessed = (from l in db.LoanProcesseds
                                             where l.first_Repayment_Date.Equals(loanDate)
                                             || l.second_Repayment_Date.Equals(loanDate)
                                             || l.third_Repayment_Date.Equals(loanDate)
                                             || l.fourth_Repayment_Date.Equals(loanDate)
                                             || l.fifth_Repayment_Date.Equals(loanDate)
                                             || l.sixth_Repayment_Date.Equals(loanDate)
                                             || l.seventh_Repayment_Date.Equals(loanDate)
                                             || l.eighth_Repayment_Date.Equals(loanDate)
                                             || l.Ninth_Repayment_Date.Equals(loanDate)
                                             || l.Tenth_Repayment_Date.Equals(loanDate)
                                             || l.Eleventh_Repayment_Date.Equals(loanDate)
                                             || l.Twelfth_Repayment_Date.Equals(loanDate)

                                             select l
                                             ).ToList();


        foreach (var item in loanProcessed)
        {

            var loan_Accountdetails = db.LoanAccounts.Where(c => c.Account_Number == item.Account_Number).FirstOrDefault();
            var loan_AccountBalance = db.LoanAccounts.Where(a => a.Account_Number == loan_Accountdetails.Account_Number).FirstOrDefault().Account_Balance;

            // Handling First Repayment
            var firstRepay = item.first_Repayment_Date;
            if (loan_AccountBalance < 0)
            {
                //continue;
                if (firstRepay != "Nill" && firstRepay != "")
                {

                    if (DateTime.ParseExact(firstRepay, "dd/MM/yyyy", CultureInfo.InvariantCulture).Date == WATTime.Date)
                    {
                        // Credit the Loan Account with the Monthly Repayment
                        try
                        {

                            var principalRepayment = item.Monthly_Repayment;

                            // Retrieve Current Account Balance First

                            var old_LoanBalance = db.LoanAccounts.Where(a => a.Account_Number == loan_Accountdetails.Account_Number).FirstOrDefault().Account_Balance;
                            var new_LoanBalance = old_LoanBalance + principalRepayment;
                            // Update the LoanAccount Balance First
                            using (var db1 = new CreditFacilityContext())
                            {
                                var result = db1.LoanAccounts.SingleOrDefault(b => b.Account_Number == item.Account_Number);
                                if (result != null)
                                {
                                    result.Account_Balance = new_LoanBalance;
                                    db1.SaveChanges();
                                }
                            }
}
// Debit the Current Account with the Monthly Repayment
                        try
                        {
                            var currentAccountDetails = db.CurrentAccounts.Where(b => b.Account_Number == loan_Accountdetails.Current_AccountNumber).FirstOrDefault();

                            var principalRepayment = item.Monthly_Repayment;
                            var old_CurrentAccountBalance = db.CurrentAccounts.Where(a => a.Account_Number == loan_Accountdetails.Current_AccountNumber).FirstOrDefault().Account_Balance;
                            var new_CurrentAccountBalance = old_CurrentAccountBalance - principalRepayment;
                            // Update the CurrentAccount Balance First
                            string connString = ConfigurationManager.ConnectionStrings["CreditFacilityContext"].ConnectionString;
                            SqlTransaction transaction1;
                            using (SqlConnection connection = new SqlConnection(connString))
                            {
                                using (SqlCommand command = new SqlCommand())
                                {
                                    connection.Open();
                                    command.Connection = connection;
                                    transaction1 = connection.BeginTransaction(IsolationLevel.Serializable);
                                    command.CommandType = CommandType.Text;
                                    command.Transaction = transaction1;
                                    command.CommandText = "UPDATE CurrentAccounts SET Account_Balance=@Account_Balance WHERE (Account_Number=@Account_Number)";
                                    command.Parameters.Add("@Account_Balance", SqlDbType.Decimal).Value = new_CurrentAccountBalance;
                                    command.Parameters.Add("@Account_Number", SqlDbType.NVarChar).Value = loan_Accountdetails.Current_AccountNumber;

                                    try
                                    {

                                        transaction1.Commit();
                                        int recordsAffected = command.ExecuteNonQuery();

                                    }
                                    catch (SqlException d)
                                    {
                                        Console.Write(d.Message, "Error in Saving");
                                    }
                                    finally
                                    {
                                        connection.Close();
                                    }

                                }

                            }

                    }
                }
            }

If an account number appears once in the loanprocessed, it works fine but if it appears there two times, it will use the same old_loanBalance for all the occurrence, There is a code to update the LoanAccount but it seems not to work if its's more than one.

// Update the LoanAccount Balance First
                        using (var db1 = new CreditFacilityContext())
                        {
                            var result = db1.LoanAccounts.SingleOrDefault(b => b.Account_Number == item.Account_Number);
                            if (result != null)
                            {
                                result.Account_Balance = new_LoanBalance;
                                db1.SaveChanges();
                            }
                        }

question from:https://stackoverflow.com/questions/65875060/issues-using-foreach-loop

Answer 1

You are using the same DbContext instance to fetch old_LoanBalance:

var old_LoanBalance = db.LoanAccounts
    .Where(a => a.Account_Number == loan_Accountdetails.Account_Number)
    .FirstOrDefault()
    .Account_Balance;

It seems you have tracking enabled so it will not actually hit the database second time when you will request the same entity. You can try reloading it with ReloadAsync:

await dbContext.Entry(old_LoanBalance).ReloadAsync();

UPD

You can also check if entity was already loaded to prevent unnecessary hitting database multiple times:

var existingInContext = db.LoanAccounts
    .Local
    .FirstOrDefault(a => a.Account_Number == loan_Accountdetails.Account_Number);

if(existingInContext != null) db.Entry(existingInContext).Reload();

var old_LoanBalance = db.LoanAccounts
    .Where(a => a.Account_Number == loan_Accountdetails.Account_Number)
    .FirstOrDefault()
    .Account_Balance;