I've got a database structure that represents investment accounts. Multiple accounts can be held by a single client, and an investment advisor can advice mulitple clients. We use M:N relationships in the database model because clients can be advised by multiple advisors and accounts can be rolled up under mulitple clients. It's like this...

Advisor... (1:M)... AdvisorClient... (M:1)... Client... (1:M)... ClientAccount... (M:1).... Account... (1:M)... Investments

I want to be able to return the total list of Investments for a given Advisor across all of their Clients and all of the Clients' accounts. In some cases I'll want to aggregate these investments but in others I'll want to return the unaggregated raw data. I'd like to be able to return the Investments list through any of these calls...

Advisor.Investments Client.Investments Account.Investments

I see how to create a M:N relationship in LLBLGen but this is a situation that's not quite so simple. Suggestions?

Thanks.

With this, always start with the entity you want to fetch, i.e InvestmentEntity. Also, keep in mind that entity data can't be aggregated into entity instances, you've to fetch a typedlist or dynamic list for that for obvious reasons as the aggregation results have different meaning than an entity field.

The relations and filters work the same though. So create a relationcollection (or RelationPredicateBucket if you're using adapter, you didn't specify that), and add to that in this order: InvestmentEntity.Relations.Account.... AccountEntity.Relations.ClientAccount.... ClientAccountEntity.Relations.Client.... ClientEntity.Relations.AdvisorClient...

We don't need Advisor added as well, as you probably have the AdvisorID which is present in the AdvisorClient as well.

So now fabricate the predicate expression to filter on this set. So add a filter: AdvisorClientFields.AdvisorID==_advisorIDToFilterOn_

This gives all investmententities.

If you want to aggregate data, you should create a dynamic list, add aggregate functions to the fields you want to aggregate on and of course a groupby collection with the fields to group on.

Thanks for the tips. It's working like a charm. Very slick!