Complex Filtered Parent-Child LINQ to SQL Query

Question

The title and tags of this post may be incorrect since my solution may end up being more MVC Controller - Model related instead of LINQ related...

I am building an MVC (not that the platform is important) site to display Invoices and the Invoice Details for approval before being paid. Basically this gives me a standard Master-Detail database schema where a single Invoice has one or many child Details.

My requirements are three-fold

1. Before the invoice as a whole can be considered approved, each detail item needs to be acknowledged by a data owner. A given invoice will have one or many owners that are keyed off of an OwnerKey column in the Details table. (In the case of a wireless invoice, the owner key would be the mobile number.) This ownership relation is maintained in a third table...let's call in Owners.
2. The page that lists the invoices also needs to display statistics...namely the total number of Detail items and the number that has been acknowledged.
3. The view that lists the invoices needs to be filtered depending on if an application admin is viewing the list or if one of the owners are. If an admin is display the page, the list of invoices and the counts are unfiltered. If an owner is viewing the page they should only see invoices where they are an owner of at least one detail item. In addition, the statistics should show how many detail items they own and how many of those they have acknowledged.

Getting the list of invoices is simple

Dim invoices = _repo.GetInvoices()

where GetInvoices() does

Return _db.Invoices

Whereas getting the list of an owners invoices is a bit more complex, it is not terrible difficult.

Dim invoices = _repo.GetUsersInvoices(username)

where GetUsersInvoices() does

Dim containingInvoices = _db.Owners.Where(Function(o) o.Owner = owner).Join(_db.InvoiceDetails, Function(d) d.OwnerKey, Function(o) o.OwnerKey, Function(o, d) New With {.InvoiceId = d.InvoiceId}).Distinct() Return _db.Invoices.Join(containingInvoices, Function(i) i.InvoiceId, Function(ci) ci.InvoiceId, Function(i, ci) i)

Because I need to calculate the number of detail items and what has been completed, instead of passing the invoices to my View, I created an InvoiceViewModel that gets passed an Invoice object and uses its references to get the count of detail items.

viewData = invoices.OrderBy(Function(i) i.Vendor).ThenBy(Function(i) i.InvoiceDate).Select(Function(i) New ViewModels.InvoiceViewModel(i))

where the constructor does

...
Dim details = invoice.InvoiceDetails
Me.NumberOfDetailItems = details.Count()
Me.NumberOfAcknowledgedDetailItems = details.Where(Function(d) Not String.IsNullOrEmpty(d.Acknowledged)).Count()
...

And then the view is called as such

Return View("Index", viewData)

So far for the admin view everything works great. My problem is when I need to introduce the filtered view on the counts.

Maybe my issue is that I am not 100% sure where the logic should exist. Do I put it in the Controller. Does it go in the Invoice Repository? Or even the InvoiceViewModel constructor?

Part of the problem is the object relationships. I can set a filter to only get certain Invoices but as soon as I type invoice.InvoiceDetails I am getting back all detail items and not the filtered ones as I needed.

Another issue is my belief (maybe incorrectly) that the InvoiceViewModel should be able to transform the passed in Invoice object to the implementation the view model was created for (namely the invoice with the addition detail count properties). However, just passing in the Invoice object gives no indication that the .InvoiceDetails property should be filtered or not. And while I could overload the constructor to accept both an Invoice object and a InvoiceDetail collection (that may or may not be filtered), that just seems ugly.

I also thought about putting the logic into my Repository [i.e. GetUsersInvoices()] and maybe forgetting the InvoiceViewModel. So instead of GetUsersInvoices() returning Invoice objects it would return an extended Invoice object that contains the extra properties (but I think I would be back to the Invoice object not knowing if the Details should be filtered or not) or maybe returning InvoiceViewModel objects.

Any thoughts or opinions would be greatly appreciated. (I guess this is what comes with having to jump in with building a somewhat big, complex new application from scratch while trying to learn two new technologies...