I'm still fairly new to C#, .NET and EfCore so please bear with me and apologies if I'm missing something in the question.
Let's say I have the following relations
public class User
{
public Account Account {get; set;}
public string Foo {get; set;}
}
public class Account
{
public List<Transaction> Transactions {get; set;}
}
public class Transaction
{
public decimal Amount {get; set;}
public TransactionType TransactionType {get; set;}
}
public enum TransactionType
{
Credit = 1,
Debit = 2,
Refund = 3,
}
I'd like to work with balances, which need to be calculated every time the User model is retrieved. For this example, let's say I need to order a list of users, where Foo is "Bar", by their account balance
var query = db.Users
.Include(u => u.Account)
.ThenInclude(a => a.Transactions)
.Where(u => u.Foo == "Bar");
var orderedQuery = query
.OrderByDescending(u =>
(u.Account.Transactions
.Where(t => t.TransactionType == TransactionType.Credit || t.TransactionType == TransactionType.Refund)
.Sum(t => t.Amount))
- u.Account.Transactions
.Where(t => t.TransactionType == TransactionType.Debit)
.Sum(t => t.Amount)
)
);
// Build the List
return orderedQuery.Skip(...).Take(...).Select(x => new SomeDTO{/* Build some DTO's with User Info, Account Info and Balance */}).ToList();
The above works, but is inefficient.
I'm working on a fairly large codebase and existing database in a asp.net core & sql-server project, so I need to work with what I've got. Can anyone suggest a better way of doing this kind of work?
Do you really need all these
Include
s? They are not necessary for the ordering aggregation later.You can optimize the ordering part of the query, by combining into a single grouping
Skip
Take
is pretty inefficient, as it requires searching through the whole list again. It may be prudent to cache it all in the client and page it afterwards.