public void DBContextTest2() { DirectoryFile intouchFile1 = new DirectoryFile("abc", "abc", Model.Enum.FileStatus.ReadFromFileSystem); intouchFile1.FileContent = FileUtility.ReadFile("..\\..\\TestFiles\\bank_20160517220202_test2.csv"); DirectoryFile intouchFile2 = new DirectoryFile("abc", "abc", Model.Enum.FileStatus.ReadFromFileSystem); intouchFile2.FileContent = FileUtility.ReadFile("..\\..\\TestFiles\\bank_20160517220202_test3.csv"); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from ODS.BankIntouch"); context.SaveChanges(); } ApplicationController.ProcessBankIntouch(intouchFile1); ApplicationController.ProcessBankIntouch(intouchFile2); int i = 0; using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.BankIntouch select row).ToList(); i = query.Count; } Assert.AreEqual(4, i); }
/// <summary> /// Process all Intouch Bank CSVs /// </summary> private static void ProcessBankIntouchData(DirectoryFile directoryFile) { List <BankIntouch> BankIntouchODS = new List <BankIntouch>(); List <BankIntouch> BankIntouchNew = new List <BankIntouch>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> BankIntouchFileString = new List <string[]>(); List <BankIntouch> BankIntouchFile = new List <BankIntouch>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankIntouchFileString, 9, true); foreach (string[] bankIntouchString in BankIntouchFileString) { BankIntouch BankIntouch = new BankIntouch { Date = Convert.ToDateTime(bankIntouchString[0]), BankDescription = bankIntouchString[1], AccountNumber = bankIntouchString[2], EmployeeName = bankIntouchString[3], Description = bankIntouchString[4], UserComments = bankIntouchString[5], Amount = Convert.ToDecimal(bankIntouchString[6]), Net = Convert.ToDecimal(bankIntouchString[7]), VAT = Convert.ToDecimal(bankIntouchString[8]) }; BankIntouchFile.Add(BankIntouch); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.BankIntouch select row).ToList(); BankIntouchODS = query; foreach (BankIntouch bankIntouch in BankIntouchFile) { if (!BankIntouchODS.Contains(bankIntouch) && !String.IsNullOrEmpty(bankIntouch.Description)) { BankIntouchNew.Add(bankIntouch); newRows++; } } foreach (BankIntouch bankIntouch in BankIntouchNew) { context.BankIntouch.Add(bankIntouch); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process all Invoice CSVs /// </summary> public static void ProcessInvoiceData(DirectoryFile directoryFile) { List <Invoice> InvoiceODS = new List <Invoice>(); List <Invoice> InvoiceNew = new List <Invoice>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> InvoiceFileString = new List <string[]>(); List <Invoice> InvoiceFile = new List <Invoice>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", InvoiceFileString, 9, true); foreach (string[] invoiceString in InvoiceFileString) { Invoice Invoice = new Invoice { Date = Convert.ToDateTime(invoiceString[0]), Description = invoiceString[1], ContractReference = invoiceString[2], ClientName = invoiceString[3], InvoiceReference = invoiceString[4], Total = Convert.ToDecimal(invoiceString[5]), VAT = Convert.ToDecimal(invoiceString[6]), Net = Convert.ToDecimal(invoiceString[7]) }; InvoiceFile.Add(Invoice); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.Invoice select row).ToList(); InvoiceODS = query; foreach (Invoice invoice in InvoiceFile) { if (!InvoiceODS.Contains(invoice)) { InvoiceNew.Add(invoice); newRows++; } } foreach (Invoice invoice in InvoiceNew) { context.Invoice.Add(invoice); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process all Intouch ExpenseEdit CSVs /// </summary> public static void ProcessExpenseEditData(DirectoryFile directoryFile) { List <ExpenseEdit> ExpenseEditODS = new List <ExpenseEdit>(); List <ExpenseEdit> ExpenseEditNew = new List <ExpenseEdit>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> ExpenseEditFileString = new List <string[]>(); List <ExpenseEdit> ExpenseEditFile = new List <ExpenseEdit>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", ExpenseEditFileString, 9, true); foreach (string[] expenseEditString in ExpenseEditFileString) { ExpenseEdit ExpenseEdit = new ExpenseEdit { Date = Convert.ToDateTime(expenseEditString[0]), Description = expenseEditString[1], Detail = expenseEditString[2], Total = Convert.ToDecimal(expenseEditString[3]), EffectiveDate = Convert.ToDateTime(expenseEditString[4]), EffectiveDescription = expenseEditString[5] }; ExpenseEditFile.Add(ExpenseEdit); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.ExpenseEdit select row).ToList(); ExpenseEditODS = query; foreach (ExpenseEdit expenseEdit in ExpenseEditFile) { if (!ExpenseEditODS.Contains(expenseEdit)) { ExpenseEditNew.Add(expenseEdit); newRows++; } } foreach (ExpenseEdit expenseEdit in ExpenseEditNew) { context.ExpenseEdit.Add(expenseEdit); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process all Intouch Bank CSVs /// </summary> public static void ProcessBankData(DirectoryFile directoryFile) { List <Bank> BankODS = new List <Bank>(); List <Bank> BankNew = new List <Bank>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> BankFileString = new List <string[]>(); List <Bank> BankFile = new List <Bank>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankFileString, 9, true); foreach (string[] bankString in BankFileString) { Bank Bank = new Bank { FITID = Convert.ToInt64(bankString[0]), PostedDate = Convert.ToDateTime(bankString[1]), Amount = Convert.ToDecimal(bankString[2]), PayeeFriendlyName = bankString[3], Activity = bankString[4], TransferType = bankString[5] }; BankFile.Add(Bank); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.Bank select row).ToList(); BankODS = query; foreach (Bank bank in BankFile) { if (!BankODS.Contains(bank)) { BankNew.Add(bank); newRows++; } } foreach (Bank bank in BankNew) { context.Bank.Add(bank); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process all Bank Cater Allen XMLs /// </summary> public static void ProcessBankCaterAllenData(DirectoryFile directoryFile) { List <BankCaterAllen> BankCaterAllenODS = new List <BankCaterAllen>(); List <BankCaterAllen> BankCaterAllenNew = new List <BankCaterAllen>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); XDocument xdoc1 = XDocument.Parse(directoryFile.FileContent); var query = from txn in xdoc1.Descendants("STMTTRN") select new BankCaterAllen { FITID = Convert.ToInt64(txn.Element("FITID").Value), TxnType = txn.Element("TRNTYPE").Value, PostedDate = Convert.ToDateTime( txn.Element("DTPOSTED").Value.Substring(0, 4) + "-" + txn.Element("DTPOSTED").Value.Substring(4, 2) + "-" + txn.Element("DTPOSTED").Value.Substring(6, 2)), Amount = Convert.ToDecimal(txn.Element("TRNAMT").Value), Name = txn.Element("NAME").Value }; List <BankCaterAllen> BankCaterAllenFile = query.ToList(); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var dbquery = (from row in context.BankCaterAllen select row).ToList(); BankCaterAllenODS = dbquery; foreach (BankCaterAllen bankCaterAllen in BankCaterAllenFile) { if (!BankCaterAllenODS.Contains(bankCaterAllen)) { BankCaterAllenNew.Add(bankCaterAllen); newRows++; } } foreach (BankCaterAllen bankCaterAllen in BankCaterAllenNew) { context.BankCaterAllen.Add(bankCaterAllen); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process all Intouch BankEdit CSVs /// </summary> public static void ProcessBankEditData(DirectoryFile directoryFile) { List <BankEdit> BankEditODS = new List <BankEdit>(); List <BankEdit> BankEditNew = new List <BankEdit>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> BankEditFileString = new List <string[]>(); List <BankEdit> BankEditFile = new List <BankEdit>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", BankEditFileString, 9, true); foreach (string[] bankEditString in BankEditFileString) { BankEdit BankEdit = new BankEdit { FITID = Convert.ToInt64(bankEditString[0]), EffectiveDate = Convert.ToDateTime(bankEditString[1]), EffectiveAmount = Convert.ToDecimal(bankEditString[2]) }; BankEditFile.Add(BankEdit); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var query = (from row in context.BankEdit select row).ToList(); BankEditODS = query; foreach (BankEdit bankEdit in BankEditFile) { if (!BankEditODS.Contains(bankEdit)) { BankEditNew.Add(bankEdit); newRows++; } } foreach (BankEdit bankEdit in BankEditNew) { context.BankEdit.Add(bankEdit); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process Salary CSV /// </summary> public static void ProcessSalaryData(DirectoryFile directoryFile) { List <Salary> SalaryDIM = new List <Salary>(); List <Salary> SalaryNew = new List <Salary>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> SalaryString = new List <string[]>(); List <Salary> SalaryFile = new List <Salary>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", SalaryString, 13, true); foreach (string[] salaryString in SalaryString) { Salary salary = new Salary { SalaryDate = Convert.ToDateTime(salaryString[1]), TaxCode = salaryString[2], TotalPay = Convert.ToDecimal(salaryString[3]), TaxDeducted = Convert.ToDecimal(salaryString[4]), EmployeeNIC = Convert.ToDecimal(salaryString[5]), EmployeePension = Convert.ToDecimal(salaryString[6]), SickPay = Convert.ToDecimal(salaryString[7]), ParentingPay = Convert.ToDecimal(salaryString[8]), StudentLoan = Convert.ToDecimal(salaryString[9]), NetPay = Convert.ToDecimal(salaryString[10]), EmployerNIC = Convert.ToDecimal(salaryString[11]), EmployerPension = Convert.ToDecimal(salaryString[12]) }; SalaryFile.Add(salary); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from DIM.Salary"); foreach (Salary salary in SalaryFile) { context.Salary.Add(salary); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to DIM from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process DividendThreshold CSV /// </summary> public static void ProcessDividendThresholdData(DirectoryFile directoryFile) { List <DividendThreshold> DividendThresholdDIM = new List <DividendThreshold>(); List <DividendThreshold> DividendThresholdNew = new List <DividendThreshold>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> DividendThresholdString = new List <string[]>(); List <DividendThreshold> DividendThresholdFile = new List <DividendThreshold>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", DividendThresholdString, 7, true); foreach (string[] dividendThresholdString in DividendThresholdString) { DividendThreshold dividendThreshold = new DividendThreshold { ThresholdID = Convert.ToInt32(dividendThresholdString[0]), FinYear = dividendThresholdString[1], MinAmount = Convert.ToDecimal(dividendThresholdString[2]), MaxAmount = Convert.ToDecimal(dividendThresholdString[3]), TaxPct = Convert.ToDecimal(dividendThresholdString[4]), IsOptimumBand = Convert.ToInt16(dividendThresholdString[5]), NetAmountCarryOver = Convert.ToDecimal(dividendThresholdString[6]) }; DividendThresholdFile.Add(dividendThreshold); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from DIM.DividendThreshold"); foreach (Model.DividendThreshold dividendThreshold in DividendThresholdFile) { context.DividendThreshold.Add(dividendThreshold); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to DIM from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process PayeeMapping CSV /// </summary> public static void ProcessPayeeMappingData(DirectoryFile directoryFile) { List <PayeeMapping> PayeeMappingDIM = new List <PayeeMapping>(); List <PayeeMapping> PayeeMappingNew = new List <PayeeMapping>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> PayeeMappingString = new List <string[]>(); List <PayeeMapping> PayeeMappingFile = new List <PayeeMapping>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", PayeeMappingString, 9, true); foreach (string[] payeeMappingString in PayeeMappingString) { PayeeMapping payeeMapping = new PayeeMapping { ID = Convert.ToInt32(payeeMappingString[0]), PayeeID = Convert.ToInt32(payeeMappingString[1]), PayeeFriendlyName = payeeMappingString[2], PayeeSourceName = payeeMappingString[3], PayeeSource = payeeMappingString[4] }; PayeeMappingFile.Add(payeeMapping); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from ODS.PayeeMapping"); foreach (PayeeMapping payeeMapping in PayeeMappingFile) { context.PayeeMapping.Add(payeeMapping); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Process TransactionType CSV /// </summary> public static void ProcessTransactionTypeData(DirectoryFile directoryFile) { List <Model.TransactionType> TransactionTypeDIM = new List <Model.TransactionType>(); List <Model.TransactionType> TransactionTypeNew = new List <Model.TransactionType>(); int newRows = 0; try { logger.Info($"Started processing file [{directoryFile.FileName}]"); List <string[]> TransactionTypeString = new List <string[]>(); List <Model.TransactionType> TransactionTypeFile = new List <Model.TransactionType>(); FileUtility.CSVStringReader(directoryFile.FileContent, ",", TransactionTypeString, 9, true); foreach (string[] transactionTypeString in TransactionTypeString) { Model.TransactionType transactionType = new Model.TransactionType { TransactionTypeID = Convert.ToInt32(transactionTypeString[0]), TransactionCategory = transactionTypeString[1], TransactionSubCategory = transactionTypeString[2], Activity = transactionTypeString[3], }; TransactionTypeFile.Add(transactionType); } using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from DIM.TransactionType"); foreach (Model.TransactionType transactionType in TransactionTypeFile) { context.TransactionType.Add(transactionType); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to ODS from file [{directoryFile.FileName}]"); logger.Info($"Finished processing file [{directoryFile.FileName}]"); } } catch (Exception e) { logger.Error($"Exception while trying to process file [{directoryFile.FileName}]. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Static method to load Salary Transactions /// </summary> public static void ProcessSalaryTransaction(ApplicationSettings application) { List <Salary> SalaryDIM = new List <Salary>(); List <Transaction> TransactionNew = new List <Transaction>(); int newRows = 0; try { logger.Info($"Started reading DIM Salary rows"); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var querySalary = (from row in context.Salary select row).ToList(); SalaryDIM = querySalary; foreach (Salary salary in SalaryDIM) { Transaction transaction = new Transaction { SourceID = "S_" + salary.SalaryDate.ToString("yyyyMMdd"), PostedDate = salary.SalaryDate, EffectiveDate = salary.SalaryDate, TransactionTypeID = -1, PayeeID = -1, TransferType = "SE", Amount = salary.NetPay, EffectiveAmount = salary.NetPay, TransactionSource = "Salary" }; TransactionNew.Add(transaction); newRows++; } AddTransaction(application, TransactionNew, "Salary"); logger.Info($"Sent [{newRows.ToString()}] Salary rows for Fact Insert"); logger.Info($"Finished reading DIM Salary rows"); } } catch (Exception e) { logger.Error($"Exception while trying to read DIM Salary rows. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Take in a list of transactions and add them to Entity if not already exists /// </summary> private static void AddTransaction(ApplicationSettings application, List <Transaction> TransactionList, string source) { List <string> TransactionFactSourceID = new List <string>(); List <Transaction> TransactionNew = new List <Transaction>(); int newRows = 0; try { logger.Info($"Started processing [{source}] transaction rows"); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var queryTransaction = (from row in context.Transaction where row.TransactionSource == source select row.SourceID).ToList(); TransactionFactSourceID = queryTransaction; foreach (Transaction transaction in TransactionList) { if (!TransactionFactSourceID.Contains(transaction.SourceID)) { TransactionNew.Add(transaction); } newRows++; } foreach (Transaction transaction in TransactionNew) { context.Transaction.Add(transaction); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows from [{source}] to Fact"); logger.Info($"Finished processing [{source}] transaction rows"); } } catch (Exception e) { logger.Error($"Exception while trying to process [{source}] transaction rows. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Static method to process Date /// </summary> public static void LoadDates(ApplicationSettings application) { DateTime startDate; DateTime endDate; int newRows = 0; try { logger.Info($"Started processing Date"); startDate = application.StartDate; endDate = application.EndDate; using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { context.Database.ExecuteSqlCommand("delete from DIM.Date"); while (DateTime.Compare(startDate, endDate) < 0) { Date newDateRow = new Date { LongDate = startDate }; newRows++; context.Date.Add(newDateRow); startDate = startDate.AddDays(1); } context.SaveChanges(); logger.Info($"Added [{newRows.ToString()}] rows to Date"); logger.Info($"Finished processing Date"); } } catch (Exception e) { logger.Error($"Exception while trying to process Date. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Static method to load Expense Transactions /// </summary> public static void ProcessExpenseTransaction(ApplicationSettings application) { List <Transaction> TransactionList = new List <Transaction>(); int newRows = 0; try { logger.Info($"Started reading SRC Expense rows"); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var queryExpense = ( from e in context.Expense join ee in context.ExpenseEdit on new { e.Date, e.Description, e.Detail, e.Total } equals new { ee.Date, ee.Description, ee.Detail, ee.Total } into eejoin from expense in eejoin.DefaultIfEmpty() join tt in context.TransactionType on new { Activity = expense.EffectiveDescription ?? e.Description } equals new { tt.Activity } orderby e.Date, e.Total select new { PostedDate = e.Date , EffectiveDate = (DateTime?)expense.EffectiveDate ?? e.Date , tt.TransactionTypeID , Total = e.Total * -1 , EffectiveTotal = e.Total * -1 , Rank = ( from o in context.Expense where (o.Date == e.Date && o.Total > e.Total) || (o.Date == e.Date && o.Total == e.Total && o.ID > e.ID) select o).Count() + 1 }); foreach (var expense in queryExpense) { string expenseID = "E_" + expense.PostedDate.ToString("yyyyMMdd") + "_" + expense.Rank.ToString(); Transaction transaction = new Transaction { SourceID = expenseID, PostedDate = expense.PostedDate, EffectiveDate = expense.EffectiveDate, TransactionTypeID = expense.TransactionTypeID, PayeeID = -1, TransferType = "NONE", Amount = expense.Total, EffectiveAmount = expense.EffectiveTotal, TransactionSource = "Expense" }; TransactionList.Add(transaction); newRows++; } AddTransaction(application, TransactionList, "Expense"); logger.Info($"Sent [{newRows.ToString()}] Expense rows for Fact Insert"); logger.Info($"Finished reading SRC Expense rows"); } } catch (Exception e) { logger.Error($"Exception while trying to read SRC Expense rows. Exception [{e.ToString()}]"); throw; } }
/// <summary> /// Static method to load Bank Transactions /// </summary> public static void ProcessBankTransaction(ApplicationSettings application) { List <Transaction> TransactionList = new List <Transaction>(); int newRows = 0; try { logger.Info($"Started reading ODS Bank rows"); using (BusinessAccountingEntities context = new BusinessAccountingEntities()) { var queryBank = ( from b in context.Bank join p in context.Payee on b.PayeeFriendlyName equals p.PayeeFriendlyName join tt in context.TransactionType on new { b.Activity } equals new { tt.Activity } join be in context.BankEdit on new { b.FITID } equals new { be.FITID } into bejoin from bank in bejoin.DefaultIfEmpty() orderby b.PostedDate, b.Amount select new { b.FITID , b.PostedDate , EffectiveDate = (DateTime?)bank.EffectiveDate ?? b.PostedDate , tt.TransactionTypeID , p.PayeeID , b.TransferType , b.Amount , EffectiveAmount = (Decimal?)bank.EffectiveAmount ?? b.Amount }); foreach (var bank in queryBank) { Transaction transaction = new Transaction { SourceID = bank.FITID.ToString(), PostedDate = bank.PostedDate, EffectiveDate = bank.EffectiveDate, TransactionTypeID = bank.TransactionTypeID, PayeeID = bank.PayeeID, TransferType = bank.TransferType, Amount = bank.Amount, EffectiveAmount = bank.EffectiveAmount, TransactionSource = "Bank" }; TransactionList.Add(transaction); newRows++; } AddTransaction(application, TransactionList, "Bank"); logger.Info($"Sent [{newRows.ToString()}] Bank rows for Fact Insert"); logger.Info($"Finished reading ODS Bank rows"); } } catch (Exception e) { logger.Error($"Exception while trying to read ODS Bank rows. Exception [{e.ToString()}]"); throw; } }