BlowFish BlowFish - 23 days ago 5
C# Question

MVC C# Creating CSV File with a 0 balance filter

I am not exactly sure how to name the title so I hope I got it close. My code is below, And everything prints out the way it should. What I am having troubles with is, any row that has an unpaid balance of 0 or less needs to not get printed. However This math is done in a different scope (I believe) so I can't say 'oh yea by the way also don't print out unpaid balance if it's zero or less.
How do I create a while/if/foreach statement that will only print rows into my csv file when the unpaid balance is less or equal to zero?

public IEnumerable<RowViewModel> GetUnpaidRR(int year, type[] types)
{
foreach (var type in types) type.Fees = type.Fees.ToList();
var transactions = _db.Transactions.Where(i => i.Id.Equals(Status.Success.Id));
return _db.Locations
.Include(i => i.Counts)
.Where(i => i.Owner.Report.Completed != null && i.Owner.Report.Id == year && i.Owner.primary == null)
.Select(location => new
{
Id = location.Owner.Report.Id,
Year = location.Owner.Report.year.Name,
Counts = location.Counts.Select(i => new
{
i.Id,
i.Count,
}),
FirstName = location.Owner.FirstName,
LastName = location.Owner.LastName,
paid = transactions.Where(t => t.Id == location.Owner.Report.Id).Select(n => new { n.Amount }).ToList(),
})
.AsEnumerable() // This takes all the transactions that match the selected customer ID
.Select(i => new RowViewModel(types)
{
Id = i.Id.HasValue ? i.Id.Value.ToIdString() : String.Empty,
Year = i.Year,
FirstName = i.FirstName,
LastName = i.LastName,
UnpaidBalance = types.Sum(j => i.Counts.Where(k => k.Id == j.Id).Sum(k => k.Count * j.Fees.Where(p => p.Id == year).Select(p => p.Cost).SingleOrDefault())) - i.paid.Sum(j => j.Amount),// this does all the math, takes the amount charged, subtracts the amount paid and produces the balance. If this is zero or less, I don't want the row to be printed.
});
}

Answer

You calculate the UnpaidBalance property in the last LINQ clause:

.Select(i => new RowViewModel(types)
{
    Id = i.Id.HasValue ? i.Id.Value.ToIdString() : String.Empty,
    Year = i.Year,
    FirstName = i.FirstName,
    LastName = i.LastName,
    UnpaidBalance = types.Sum(j => i.Counts.Where(k => k.Id == j.Id).Sum(k => k.Count * j.Fees.Where(p => p.Id == year).Select(p => p.Cost).SingleOrDefault())) - i.paid.Sum(j => j.Amount)
})

Simply add a .Where() clause after that:

.Where(i => i.UnpaidBalance <= 0)

If performance is an issue you may be able to move the calculation and filter to earlier in the overall sequence of events, but if all you need to do is filter results then that's exactly what .Where() is for.

(Note: There is some confusion in how you describe the filter. In some places you want to exclude values <= 0, and in other places you want to exclusively include values <= 0. Whatever logic you actually need, however, would simply go in this .Where() clause.)