/// <summary>
/// <see cref="IRentals.GetNumberOfPayees()"/>
/// </summary>
public int GetNumberOfPayees()
{
using (var context = new RentalsContext())
{
return(context.Payees.Count());
}
}
/// <summary>
/// <see cref="IRentals.GetNumberOfCategories()"/>
/// </summary>
public int GetNumberOfCategories()
{
using (var context = new RentalsContext())
{
return(context.Categories.Count());
}
}
/// <summary>
/// <see cref="IRentals.GetAllCategories()"/>
/// </summary>
public ICollection <Category> GetAllCategories()
{
using (var context = new RentalsContext())
{
return(context.Categories.AsNoTracking().ToList());
}
}
/// <summary>
/// <see cref="IRentals.GetNumberOfAccounts"/>
/// </summary>
public int GetNumberOfAccounts()
{
using (var context = new RentalsContext())
{
return(context.Accounts.Count());
}
}
/// <summary>
/// <see cref="IRentals.GetNumberOfTransactions()"/>
/// </summary>
public int GetNumberOfTransactions()
{
using (var context = new RentalsContext())
{
return(context.Transactions.Count());
}
}
/// <summary>
/// <see cref="IRentals.SaveUpdatedCategory(Category)"/>
/// </summary>
public void SaveUpdatedCategory(Category category)
{
using (var context = new RentalsContext())
{
context.Entry(category).State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.SaveNewCategory(Category)"/>
/// </summary>
public void SaveNewCategory(Category category)
{
using (var context = new RentalsContext())
{
context.Categories.Add(category);
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.FindTransaction(int?)"/>
/// </summary>
public Transaction FindTransaction(int?id)
{
using (var context = new RentalsContext())
{
return(context.Transactions.AsNoTracking()
.SingleOrDefault(p => p.Id == id));
}
}
/// <summary>
/// <see cref="IRentals.SaveNewTransaction(Transaction)"/>
/// </summary>
public void SaveNewTransaction(Transaction transaction)
{
using (var context = new RentalsContext())
{
context.Transactions.Add(transaction);
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.SaveUpdatedAccount(Account)"/>
/// </summary>
public void SaveUpdatedAccount(Account account)
{
using (var context = new RentalsContext())
{
context.Entry(account).State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.SaveNewAccount(Account)"/>
/// </summary>
public void SaveNewAccount(Account account)
{
using (var context = new RentalsContext())
{
context.Accounts.Add(account);
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.SaveUpdatedTransaction(Transaction)"/>
/// </summary>
public void SaveUpdatedTransaction(Transaction transaction)
{
using (var context = new RentalsContext())
{
context.Entry(transaction).State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.FindCategory(int?)"/>
/// </summary>
public Category FindCategory(int?id)
{
using (var context = new RentalsContext())
{
return(context.Categories.AsNoTracking()
.SingleOrDefault(c => c.Id == id));
}
}
/// <summary>
/// <see cref="IRentals.SaveUpdatedPayee(Payee)"/>
/// </summary>
public void SaveUpdatedPayee(Payee payee)
{
using (var context = new RentalsContext())
{
context.Entry(payee).State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.SaveNewPayee(Payee)"/>
/// </summary>
public void SaveNewPayee(Payee payee)
{
using (var context = new RentalsContext())
{
context.Payees.Add(payee);
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.FindPayee(int?)"/>
/// </summary>
public Payee FindPayee(int?id)
{
using (var context = new RentalsContext())
{
return(context.Payees.AsNoTracking()
.Include(p => p.DefaultCategory)
.SingleOrDefault(p => p.Id == id));
}
}
/// <summary>
/// <see cref="IRentals.RemoveTransaction(Transaction)"/>
/// </summary>
public void RemoveTransaction(int id)
{
using (var context = new RentalsContext())
{
Transaction transaction = context.Transactions.Find(id);
context.Transactions.Remove(transaction);
context.SaveChanges();
}
}
/// <summary>
/// <see cref="IRentals.FindTransactionWithAccountAndPayeeAndCategory(int?)"/>
/// </summary>
public Transaction FindTransactionWithAccountAndPayeeAndCategory(int?id)
{
using (var context = new RentalsContext())
{
return(context.Transactions.AsNoTracking()
.Include(t => t.Account)
.Include(t => t.Payee)
.Include(t => t.Category)
.SingleOrDefault(p => p.Id == id));
}
}
/// <summary>
/// <see cref="IRentals.GetAllPayees()"/>
/// </summary>
public ICollection <Payee> GetAllPayees()
{
using (var context = new RentalsContext())
{
return(context.Payees
.AsNoTracking()
.Include(p => p.DefaultCategory)
.AsNoTracking()
.ToList());
}
}
/// <summary>
/// Drop and re-add the database instance with or without seeded data
/// </summary>
/// <param name="withSeed"></param>
public static void NewDb(bool withSeed = true)
{
Database.SetInitializer(new DropCreateDatabaseAlways <RentalsContext>());
using (var context = new RentalsContext())
{
context.Database.Initialize(true);
if (withSeed)
{
PrepareData(context);
}
}
}
/// <summary>
/// <see cref="IRentals.GetAllAccounts()"/>
/// </summary>
public ICollection <Account> GetAllAccounts()
{
using (var context = new RentalsContext())
{
var accounts = context.Accounts.AsNoTracking().ToList();
foreach (var account in accounts)
{
account.Balance = GetAccountBalance(account.Id);
}
return(accounts);
}
}
/// <summary>
/// <see cref="IRentals.FindAccountWithTransactions(int?, DateTime?, DateTime?, bool)"/>
/// </summary>
public Account FindAccountWithTransactions(int?id, DateTime?from = null, DateTime?to = null, bool ascending = true)
{
using (var context = new RentalsContext())
{
var account = context.Accounts
.SingleOrDefault(a => a.Id == id);
if (account != null)
{
context.Accounts.Attach(account);
var transactions = context.Entry(account)
.Collection(a => a.Transactions)
.Query()
.Include(t => t.Payee)
.Include(t => t.Category)
.OrderBy(t => t.Date)
.ThenBy(t => t.Id);
CalculateTransactionBalances(transactions, account.OpeningBalance);
if (from == null)
{
from = DateTime.MinValue;
}
if (to == null)
{
to = DateTime.MaxValue;
}
if (ascending)
{
account.Transactions = transactions
.Where(t => t.Date >= from && t.Date <= to)
.ToList();
}
else
{
account.Transactions = transactions
.Where(t => t.Date >= from && t.Date <= to)
.OrderByDescending(t => t.Date)
.ThenByDescending(t => t.Id)
.ToList();
}
account.Balance = GetAccountBalance(id);
}
return(account);
}
}
/// <summary>
/// <see cref="IRentals.FindPayeeWithTransactions(int?, DateTime?, DateTime?, bool)"/>
/// </summary>
public Payee FindPayeeWithTransactions(int?id, DateTime?from = null, DateTime?to = null, bool ascending = true)
{
using (var context = new RentalsContext())
{
var payee = context.Payees
.Include(p => p.DefaultCategory)
.SingleOrDefault(a => a.Id == id);
if (payee != null)
{
context.Payees.Attach(payee);
if (from == null)
{
from = DateTime.MinValue;
}
if (to == null)
{
to = DateTime.MaxValue;
}
if (ascending)
{
context.Entry(payee)
.Collection(c => c.Transactions)
.Query()
.Include(t => t.Account)
.Include(t => t.Category)
.Where(t => t.Date >= from && t.Date <= to)
.OrderBy(t => t.Date)
.ThenBy(t => t.Id)
.Load();
}
else
{
context.Entry(payee)
.Collection(c => c.Transactions)
.Query()
.Include(t => t.Account)
.Include(t => t.Category)
.Where(t => t.Date >= from && t.Date <= to)
.OrderByDescending(t => t.Date)
.ThenByDescending(t => t.Id)
.Load();
}
}
return(payee);
}
}
/// <summary>
/// <see cref="IRentals.GetAllTransactionsWithAccountAndPayeeAndCategory(String, String, String, DateTime?, DateTime?, bool)"/>
/// </summary>
public ICollection <Transaction> GetAllTransactionsWithAccountAndPayeeAndCategory(
String account = null, String payee = null, String category = null,
DateTime?from = null, DateTime?to = null, bool ascending = true)
{
using (var context = new RentalsContext())
{
var transactions = context.Transactions.AsNoTracking()
.Include(t => t.Account)
.Include(t => t.Payee)
.Include(t => t.Category)
.AsQueryable();
if (from == null)
{
from = DateTime.MinValue;
}
if (to == null)
{
to = DateTime.MaxValue;
}
if (!String.IsNullOrEmpty(account) || !String.IsNullOrEmpty(payee) || !String.IsNullOrEmpty(category))
{
// Make search an OR operation - any of the matching search terms is included in the results
transactions = transactions.Where(t =>
(!String.IsNullOrEmpty(account) && t.Account.Name.ToLower().Contains(account.ToLower())) ||
(!String.IsNullOrEmpty(payee) && t.Payee.Name.ToLower().Contains(payee.ToLower())) ||
(!String.IsNullOrEmpty(category) && t.Category.Name.ToLower().Contains(category.ToLower()))
);
}
if (ascending)
{
transactions = transactions.AsQueryable()
.Where(t => t.Date >= from && t.Date <= to)
.OrderBy(t => t.Date)
.ThenBy(t => t.Id);
}
else
{
transactions = transactions.AsQueryable()
.Where(t => t.Date >= from && t.Date <= to)
.OrderByDescending(t => t.Date)
.ThenByDescending(t => t.Id);
}
return(transactions.ToList());
}
}
/// <summary>
/// <see cref="IRentals.GetTotalOfAccountBalances()"/>
/// </summary>
public Decimal GetTotalOfAccountBalances()
{
using (var context = new RentalsContext())
{
var balance = 0.0m;
foreach (var account in context.Accounts)
{
balance += GetAccountBalance(account.Id);
}
return(balance);
}
}
/// <summary>
/// <see cref="IRentals.FindAccount(int?)"/>
/// </summary>
public Account FindAccount(int?id)
{
using (var context = new RentalsContext())
{
var account = context.Accounts.AsNoTracking()
.SingleOrDefault(a => a.Id == id);
if (account != null)
{
account.Balance = GetAccountBalance(id);
}
return(account);
}
}
/// <summary>
/// <see cref="IRentals.GetAccountBalance(int?)"/>
/// </summary>
public Decimal GetAccountBalance(int?id)
{
using (var context = new RentalsContext())
{
var openingBalance = (from a in context.Accounts
where a.Id == id
select a.OpeningBalance).FirstOrDefault();
// Generates SQL that gets only the specific column
var amounts = (from t in context.Transactions
where t.AccountId == id
select new { Amount = t.Amount, CategoryType = t.Category.Type }).ToList();
var income = amounts.Where(a => a.CategoryType == CategoryType.Income).ToList().Sum(a => a.Amount);
var expense = amounts.Where(a => a.CategoryType == CategoryType.Expense).ToList().Sum(a => a.Amount);
return(openingBalance + income - expense);
}
}
/// <summary>
/// Seed the context with some representative dummy data.
/// </summary>
/// <param name="context">The Context to seeded with data.</param>
public static void PrepareData(RentalsContext context)
{
var accountsToAdd = new List <Account>()
{
new Account()
{
Name = "BankAccount1", OpeningBalance = 100.99m
},
new Account()
{
Name = "BankAccount2"
},
new Account()
{
Name = "BankAccount3", OpeningBalance = 1000.00m
},
new Account()
{
Name = "Sortable Account", OpeningBalance = 1.00m
}
};
context.Accounts.AddRange(accountsToAdd);
context.SaveChanges();
var accountsAdded = context.Accounts.ToList();
var categoriesToAdd = new List <Category>()
{
new Category()
{
Name = "Rental Income", Type = CategoryType.Income
},
new Category()
{
Name = "Bank Interest", Type = CategoryType.Income
},
new Category()
{
Name = "Utilities", Type = CategoryType.Expense
},
new Category()
{
Name = "Bank Charges", Type = CategoryType.Expense
},
new Category()
{
Name = "Sortable Income", Type = CategoryType.Income
},
};
context.Categories.AddRange(categoriesToAdd);
context.SaveChanges();
var categoriesAdded = context.Categories.ToList();
var payeesToAdd = new List <Payee>()
{
new Payee()
{
Name = "Renter A", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Rental Income").Single().Id
},
new Payee()
{
Name = "Renter B", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Rental Income").Single().Id
},
new Payee()
{
Name = "MyBank Interest", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Bank Interest").Single().Id, Memo = "Paid Monthly"
},
new Payee()
{
Name = "MyBank Charges", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Bank Charges").Single().Id
},
new Payee()
{
Name = "Gas Supplier", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Utilities").Single().Id
},
new Payee()
{
Name = "Electricity Supplier", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Utilities").Single().Id, Memo = "For Quarter Feb - May"
},
new Payee()
{
Name = "Sortable Payee", DefaultCategoryId = categoriesAdded.Where(c => c.Name == "Sortable Income").Single().Id
},
};
context.Payees.AddRange(payeesToAdd);
context.SaveChanges();
var payeesAdded = context.Payees.Include(p => p.DefaultCategory).ToList();
var defaultTransactionDate = new DateTime(2017, 1, 1);
var today = DateTime.Today;
var transactionsToAdd = new List <Transaction>()
{
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "BankAccount1").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Renter A").Single().Id,
CategoryId = payeesAdded.Where(p => p.Name == "Renter A").Single().DefaultCategoryId,
Amount = 10.00m, Date = defaultTransactionDate
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "BankAccount1").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Renter B").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Rental Income").Single().Id,
Amount = 100.00m, Date = defaultTransactionDate
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "BankAccount2").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Renter A").Single().Id,
CategoryId = payeesAdded.Where(p => p.Name == "Renter A").Single().DefaultCategoryId,
Amount = 200.00m, Date = defaultTransactionDate.AddDays(1)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "BankAccount2").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Gas Supplier").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Utilities").Single().Id,
Amount = 200.00m, Date = defaultTransactionDate.AddDays(2), Memo = "For Quarter May - Aug"
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "BankAccount3").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "MyBank Charges").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Bank Charges").Single().Id,
Amount = 30.00m, Date = defaultTransactionDate.AddDays(3)
},
// Add some transactions that can be searched and sorted
// Use Amount to indicate the order in which transcations should appear
// i.e. Oldest by date first, then by id where dates are equal
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 1.00m, Date = today.AddMonths(-24)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 3.00m, Date = today.AddMonths(-6)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 5.00m, Date = today.AddMonths(-3)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 6.00m, Date = today.AddMonths(-1)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 7.00m, Date = today.AddDays(-7)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 8.00m, Date = today
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 2.00m, Date = today.AddMonths(-12)
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 9.00m, Date = today
},
new Transaction()
{
AccountId = accountsAdded.Where(a => a.Name == "Sortable Account").Single().Id,
PayeeId = payeesAdded.Where(p => p.Name == "Sortable Payee").Single().Id,
CategoryId = categoriesAdded.Where(p => p.Name == "Sortable Income").Single().Id,
Amount = 4.00m, Date = today.AddMonths(-4)
},
};
context.Transactions.AddRange(transactionsToAdd);
context.SaveChanges();
Accounts = accountsAdded;
Categories = categoriesAdded;
Payees = payeesAdded;
Transactions = context.Transactions.ToList();
}
