Trying to figure out if something is possible. We are evaluating LLBLGen Pro for an application.

Setup: LLBLGen 2.0 .Net Framework 3.0 Oracle 10 database Oracle 9 Client ODP.Net for Oracle 8/9

The situation: 1. Application table holds insurance applications. 2. Benefit table holds insurance benefit info, foreign key to AppID on Application. Multiple benefits occur. 3. Alias table holds information about the people applying for insurance, foreign key to AppID on Application. Multiple insureds occur. 4. Let's get complex. AliasBenefit holds information about who is assigned to which benefits. Different Aliases can be assigned to different Benefits (only requirement is that one Alias be assigned to each Benefit). Foreign keys are AppID, AliasID, and BenefitID. 5. Exclusion table holds things we won't cover (pre-existing conditions and such). They can be assigned at the Alias level or the AliasBenefit level. Foreign key values are AppID, BenefitID - nullable, and AliasID.

Attched is an image showing the basic data model along with key fields by table (sorry about the crappy handwriting and background text, I drew this on the back of a page of LLBLGen documentation...).

Two questions.

1. Can/How I recursively save the AliasBenefit information?

2. Can/How I recursively save the Exclusion information? I was thinking of creating a sub-type of Exclusion for the Alias only scenario.

I'm thinking these things are possible, but I'm just not sure what I'm looking for in terms of getting it done.

I've search around a bit but I'm not sure exactly what key words to use to describe the situation.

Thanks in advance for your consideration.

Thanks, Jason

UPDATE, I HAVE A SOLUTION WORKED OUT, SEE BELOW

Sample, an application with 2 aliases (A1 and A2) and 2 benefits (B1 and B2).

B1 has A1 and A2 as aliases. B2 has A2 as the only alias.

This requires three aliasBenefit records (AB, aliasBenefit contains the benefit and alias relationships as well as other data fields): AB1 tying alias A1 to benefit B1 AB2 tying alias A2 to benefit B1 AB3 tying alias A2 to benefit B2

I’m adding the aliasBenefit records to both the alias and the benefit (it works for key propagation, I’m not sure what issues this might cause), this seems to work pretty well.

Here’s code that works that does all of this (using functions to return populated entity objects, Refactor! rocks).

The database results are exactly as expected.

Thanks for anyone who considered this problem. My company will probably be going this route, LLBLGen Pro rocks. Kudos on the DotNetRocks interview as well.

Code: UwApplEntity appl = CreateAppl(AppID);

        UwApplAliasEntity applAlias = CreateApplAlias(AppID, "tstdat-643712");
        appl.UwApplAlias.Add(applAlias);

        UwApplAliasEntity applAlias2 = CreateApplAlias(AppID + 1, "tstdat-643812");
        appl.UwApplAlias.Add(applAlias2);

        UwApplBenefitEntity benefit = CreateBenefit(AppID);
        appl.UwApplBenefit.Add(benefit);

        UwApplBenefitEntity benefit2 = CreateBenefit(AppID+1);
        appl.UwApplBenefit.Add(benefit2);

        // create and populate appl alias
        UwApplAliasBenefitEntity aliasBenefit = CreateAliasBenefit();

        applAlias.UwApplAliasBenefit.Add(aliasBenefit);
        benefit.UwApplAliasBenefit.Add(aliasBenefit);

        // create and populate appl alias2
        UwApplAliasBenefitEntity aliasBenefit2 = CreateAliasBenefit();

        applAlias2.UwApplAliasBenefit.Add(aliasBenefit2);
        benefit.UwApplAliasBenefit.Add(aliasBenefit2);

        // create and populate appl alias3
        UwApplAliasBenefitEntity aliasBenefit3 = CreateAliasBenefit();

        applAlias2.UwApplAliasBenefit.Add(aliasBenefit3);
        benefit2.UwApplAliasBenefit.Add(aliasBenefit3);

        // create and populate alias exclusion
        UwApplAliasExclusionEntity exclusion = CreateExclusion(AppID);
        aliasBenefit.UwApplAliasExclusion.Add(exclusion);

and then

adapter.SaveEntity(appl,true, true);

You got it right.

I only have a comment about the data model. IMHO, the following might be another possible data model:

Application

appID (PK) otherFields..

AppAliasBenefitItems

appAliasBenefitID (PK) appID (FK) otherFields..

Alias

aliasID (PK) appAliasBenefitID (FK) (Unique Constraint) otherFields..

Benefit

benefitID (PK) appAliasBenefitID (FK) (Unique Constraint) otherFields..