Estoy tratando de usar el bucle Foreach para calcular el interés pagado por aquellos que recibieron préstamos en mi solicitud. La aplicación carga la lista de todos los préstamos emitidos, aquellos cuyo saldo de la cuenta del préstamo es menor que cero se cargan para el reembolso. Funciona bien si la persona ha recibido el préstamo solo una vez, pero si lo recibe dos o tres veces, el préstamo utiliza el mismo saldo anterior para las tres instancias.

A continuación se muestra el código:

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();
                                    }

                                }

                            }

                    }
                }
            }

Si un número de cuenta aparece una vez en el préstamo procesado , funciona bien, pero si aparece allí dos veces, usará el mismo old_loanBalance para todas las ocurrencias. Hay un código para actualizar la cuenta de préstamo pero parece no trabajar si es más de uno.

// 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();
                            }
                        }
-1
uthumvc 24 ene. 2021 a las 22:37

1 respuesta

La mejor respuesta

Está utilizando la misma instancia de DbContext para recuperar old_LoanBalance:

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

Parece que tiene el seguimiento habilitado, por lo que en realidad no llegará a la base de datos por segunda vez cuando solicite la misma entidad. Puede intentar volver a cargarlo con ReloadAsync:

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

UPD

También puede verificar si la entidad ya estaba cargada para evitar que la base de datos de golpe innecesaria varias veces:

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;
1
Guru Stron 29 ene. 2021 a las 00:18