LINQ-to-SQL GroupBy across Many-to-Many Divide?

Var q = from I in Invoice join a in Account on i. AccountID equals a. AccountID join sa in StructureAccount on i.

AccountID equals sa. AccountID join s in Structure on sa. StructureID equals s.

StructureID group I by s. StructureID I tested it on the following dummy data: var Invoice = new { new { InvoiceID = 1, AccountID = 1 }, new { InvoiceID = 2, AccountID = 2 }, new { InvoiceID = 3, AccountID = 3 }, new { InvoiceID = 4, AccountID = 1 }, new { InvoiceID = 5, AccountID = 2 }, new { InvoiceID = 6, AccountID = 3 } }; var Account = new { new { AccountID = 1 }, new { AccountID = 2 }, new { AccountID = 3 }, }; var StructureAccount = new { new { AccountID = 1, StructureID = 2 }, new { AccountID = 1, StructureID = 3 }, new { AccountID = 2, StructureID = 2 }, new { AccountID = 3, StructureID = 1 }, new { AccountID = 3, StructureID = 2 }, }; var Structure = new { new { StructureID = 1 }, new { StructureID = 2 }, new { StructureID = 3 } } And it returns: StructureID = 2: InvoiceID's: 1,2,3,4,5,6 StructureID = 3: InvoiceID's: 1,4 StructureID = 1: InvoiceID's: 3,6.

Var q = from I in Invoice join a in Account on i. AccountID equals a. AccountID join sa in StructureAccount on i.

AccountID equals sa. AccountID join s in Structure on sa. StructureID equals s.

StructureID group I by s. StructureID; I tested it on the following dummy data: var Invoice = new { new { InvoiceID = 1, AccountID = 1 }, new { InvoiceID = 2, AccountID = 2 }, new { InvoiceID = 3, AccountID = 3 }, new { InvoiceID = 4, AccountID = 1 }, new { InvoiceID = 5, AccountID = 2 }, new { InvoiceID = 6, AccountID = 3 } }; var Account = new { new { AccountID = 1 }, new { AccountID = 2 }, new { AccountID = 3 }, }; var StructureAccount = new { new { AccountID = 1, StructureID = 2 }, new { AccountID = 1, StructureID = 3 }, new { AccountID = 2, StructureID = 2 }, new { AccountID = 3, StructureID = 1 }, new { AccountID = 3, StructureID = 2 }, }; var Structure = new { new { StructureID = 1 }, new { StructureID = 2 }, new { StructureID = 3 } }; And it returns: StructureID = 2: InvoiceID's: 1,2,3,4,5,6 StructureID = 3: InvoiceID's: 1,4 StructureID = 1: InvoiceID's: 3,6.

I'll assume you have the following starting point: IQueryable _invoices; First, you need to get a list of all the items that you will be iterating over: IQueryable _accounts = _invoices. Select(myInvoice => myInvoice. Account).Distinct(); IQueryable _structuredAccounts = _accounts.

SelectMany(myAccount => myAccount. StructuredAccounts); IQueryable _structures = _structuredAccounts. Select(myStructuredAccount => myStructuredAccount.

Structure).Distinct(); Next, you need to go back and join your Structure objects to the respective Invoice objects. For this, you'll: Get a set of {Structure, Account} pairs: var structureAccountJoin = _structures. Join(_structuredAccounts, _structure => structure.

StructuredID, _structuredAccount => _structuredAccount. StructuredID, (structure, structuredAccount) => new { Structure = structure, Account = structuredAccount. Account }); Get a set of {Structure, Invoice} pairs: var structureInvoiceJoin = structureAccountJoin.

Join(_invoices, myObj => myObj.Account. AccountID, invoice => invoice. AccountID, (myObj, invoice) => new { Structure = myObj.

Structure, Invoice = invoice}); Finally, you can group everything by the Structure object: IQueryable> groupByStructure = structureInvoiceJoin. GroupBy(obj => obj. Structure, result => result.

Invoice); (GroupBy documentation: http://msdn.microsoft.com/en-us/library/bb534304.aspx) Now, you can access everything as follows: foreach(IGrouping groupEntry in groupByStructure) { Structure currentGrouping = groupEntry. Key; foreach(Invoice inv in groupEntry) { // do something; } } As a note, this is a very complex script that requires a lot of steps if you don't have access to the tables directly. You may want to look into creating a StoredProcedure for this instead, as it will be more efficient and you'll be able to use SQL Joins instead.

If you have only an IQueryable to work with and access to nothing else, there is probably a design problem somewhere in your architecture. Nevertheless, this is the way to make it work based on your requirements, if I read them correctly.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.