llblgenpro: Adapter version 2.0. dbase: sql2000.

Hi, below are the snippets of the 2 codes:

Snippet 1:

private static IPrefetchPath2 PrefetchPath4ClaimBillLines()
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)MW.DAL.EntityType.ClaimBillLineEntity);
            prefetchPath.Add(MyClaimBillLineEntity.PrefetchPathClaimBill);
            return prefetchPath;
        }

public static EntityCollection<MyClaimBillLineEntity> GetClaimBillLines(PredicateExpression expression)
        {
            IPrefetchPath2 prefetchPath = PrefetchPath4ClaimBillLines();

            IRelationPredicateBucket bucket = new RelationPredicateBucket(expression);

            EntityCollection<MyClaimBillLineEntity> claimBillLines = new EntityCollection<MyClaimBillLineEntity>(new MyClaimBillLineEntityFactory());
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(claimBillLines, bucket, prefetchPath);

            return claimBillLines;
        }

Snippet 2:

private static IPrefetchPath2 PrefetchPath4ClaimBillIcd9()
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)MW.DAL.EntityType.ClaimBillIcd9Entity);
            prefetchPath.Add(MyClaimBillIcd9Entity.PrefetchPathIcd9);
            return prefetchPath;
        }

public static EntityCollection<MyClaimBillIcd9Entity> GetClaimBillIcd9s(PredicateExpression expression)
        {
            IPrefetchPath2 prefetchPath = PrefetchPath4ClaimBillIcd9();

            IRelationPredicateBucket bucket = new RelationPredicateBucket(expression);
            bucket.Relations.Add(ClaimBillIcd9Entity.Relations.Icd9EntityUsingIcdCode);

            EntityCollection<MyClaimBillIcd9Entity> claimBillIcd9s = new EntityCollection<MyClaimBillIcd9Entity>(new MyClaimBillIcd9EntityFactory());
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(claimBillIcd9s, bucket, prefetchPath);
            return claimBillIcd9s;
        }

The relationships are as follow: relationship1:

claimbillline.claimbillid = claimbill.id (n:1 relationship and claimbill.id is integer type)

relationship2:

claimbillicd9.icdcode = icd9.id (n:1 relationship and icd9.id is string type)

Problem The first snippet works fine. i.e. I can retrieve the claimbill for each claimbillline. In the second snippet I cannot retrieve icd9 for each claimbillicd9. The value is always null

Question 1. Does llblgenpro has issue with "using prefetchPath in the relationship where the PF key relationship is of type String"? 2. If yes, then is there any work around? 3. If no, then what did I do wrong?

Thanks.

BZ

Hi BZ,

1. If no, then what did I do wrong?

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)MW.DAL.EntityType.ClaimBillIcd9Entity);
            prefetchPath.Add(MyClaimBillIcd9Entity.PrefetchPathIcd9);
            return prefetchPath;

Why the prefetchPath root element is ClaimBillIcd9Entity while next you are adding MyClaimBillIcd9Entity.PrefetchPathIcd9 ? Did you extend your _ClaimBillIcd9Entity _by making _MyClaimBillIcd9Entity _or is a typo?

Anyway, could you please post the generated SQL for snippet 2? (See LLBLGenPro Help - Using generated code - Troubleshooting and Debugging).

I think he uses two-classes templates for Adapter. so change: bucket.Relations.Add(ClaimBillIcd9Entity.Relations.Icd9EntityUsingIcdCode); into bucket.Relations.Add(MyClaimBillIcd9Entity.Relations.Icd9EntityUsingIcdCode);

Otis, Yes I use 2 classes template.

all,

claimbillicd9.icdcode = icd9.id (n:1 relationship and icd9.id is string type)

There was nothing wrong in the query. The icd9.id field is varchar type and some of the records have error where there are trailing spaces. When I type in the data in claimbillicd9.icdcode that misses trailing spaces, sql2000 is able to resolve the PF key relationship despite the record has missing trailing spaces. However when I do querry and resolve the relationship, the PF key do not match thus I cannot retrieve icd9 for each claimbillicd9.

Thanks.

Regarding the string PK & FK, what's their database data type? Are they similar, as some databases might allow different dataTypes to reference each other?

walaa,

They are both varchar(5). It turned out that the data in the PK of the one table is "810.1 ". The data in the FK of the other table is "810.1". SQL doesn't care about this so they did not complain about FK not matching with a PK. However doing querry in Llblgen does matter. I cannot resolve the PK-FK relationship.

Any how, this was due to bad data so I fixed the data and everything is OK.

Thanks.

BZ