Linq grouping based on other groups

Question

I am storing transactions in a table. These transactions have an ID which relates to their parent node in a hierarchy tree. An example of my tree is shown in the image below. I need to allow an end user to retrieve the transaction and group them however they want as they are then written to a file based on the grouping. In the image below the devices (IM001-1 etc) create the transactions. In the live version the tree will be a lot larger and could have further levels for town and smaller regions etc. Now to the problem. Let’s say that a user wants all the transactions from the “UK” devices and they want them grouped by the country node (so “UK_North” and “UK_South”). The result should be a grouped list which in this example would contain two items. The first item would contain all the transactions from devices “IM001-1” and “IM001-2” and the second item would have the transactions for devices “IM002-1” and “IM002-2”. What I have done so far is to get a list of device for each parent node. So I have a grouped list where each item contains Ids of all its child devices. What I’d like to do is use Linq to look at all my transactions with their ID’s and create a grouped list where the grouping checks to see which parent owns it. This all sounds rather complex so if it’s not clear I can provide additional details if required. Am I approaching this problem the right way ? As there could be a lot of transactions I didn’t want to have to do a lot of tree navigation for every transaction because of the performance hit.

The transactions are held in a list of transaction:

 List<Transaction> transactions

There is a property on each transaction that exposes its “DeviceId”

I then have a grouped list which is defined as the following when I hover the mouse over the “var”:

 IEnumerable<IGrouping<String,Device>>

This list is called “groupedDevices”. Each item in the list represents a parent node (for example UK_North) which contains a list of all the available devices under that node. Each device has a “DeviceId” property.

What the Linq statement needs to do is look at the transactions list and see which parent it belongs to by using the “groupedDevices” list and then grouping by that.

Answer 1

Without knowing the classes you have, it's hard to write the actual query, but something like this should work:

from country in countries
from device in country.Devices
from transaction in device.Transactions
group transaction by country

Or:

from country in countries
select new
{
  Country = country,
  Transactions = from device in country.Devices
                 from transaction in device.Transactions
                 select transaction
}

EDIT:

For the structures you provided, you can use the following query:

from g in groupedDevices
from device in g
from transaction in transactions
where transaction.DeviceId == device.Id
group transaction by g