xterm wroteI didn't even try to compile this, but see if it makes any sense for you (It definitely has errors)
var view_result = db.Invoices.Select(
invoice =>
new InvoiceGridVM {
InvoiceID = invoice.InvoiceID,
Partner = invoice.Partner.Name,
TotalQuantity = invoice.Transactions.Sum(t => t.Quantity),
NetTotal = invoice.Transactions.Sum(t => (t.Quantity * t.UnitPrice)*(t.Discount/100)),
SubTotal = invoice.Transactions.Sum(t => (t.Quantity * t.UnitPrice)*(t.Discount/100))*1.1) //VAT
}
);
Returns IEnumerable<InvoiceGridVM>
Very close to what I attempted do... until my brain crashed :)
I had to tweak your code a little bit to get it working. And now it is! But I want a way to limit the digits after the point to only 2 digits.
Here's the code:
var view_result = harbDB.Invoices.Select(invoice =>
new InvoiceGridVM
{
InvoiceID = invoice.InvoiceID,
Partner = invoice.Partner.Name,
TotalQuantity = invoice.Transactions.Sum(t => t.Quantity),
NetTotal = invoice.Transactions.Sum(t => (t.Quantity * t.UnitPrice) * (1 - (t.Discount / 100))), //you have to substract from one to get the value of the discount
SubTotal = invoice.Transactions.Sum(t => ((t.Quantity * t.UnitPrice) * (1 - (t.Discount / 100))) * 1.1) //VAT
}
).ToList();
Note: As you can see in my previous post, I had the SubTotal and NetTotal set as decimals, and then inside the LINQ expression I tried using Convert.ToDecimal which threw an exception. Hence I changed them in the VM to be of data type double.
One more question, can I retrieve the InvoiceID but not display it in the DataGridView control? I just want to have it attached/available in the InvoiceGridVM object...