Versions: LLBLGen Pro 2.0 Final, VS2005, Windows XP, SQL Server 2000

I am new to LLBLGen Pro. I am trying to load data and filter that data based on relevant, but non-key data.

The data structure is Bill (many) >------ (one) Account (one) -----< (many) AccountDetail

An Account may appear on many different bills. AccountDetail records reflect information in effect for the account at different times based on an EffectiveDate and TerminationDate.

What I have been trying to do is work out a prefetch strategy that will select only the AccountDetail records that are in effect for the date range of the bill. In SQL this would be:

Select * 
from Bill
inner join Account on BillID
inner join AccountDetail on AccountID
where Bill.StartDate >= AccountDetail.EffectiveDate and
(AccountDetail.TerminationDate is null OR AccountDetail.TerminationDate <= Bill.EndDate)

Also, I am prefetching several different relations off of bill. To simplify my initial attempts, I left off the TerminationDate conditions and am just trying to check EffectiveDate.

I tried setting up my prefetch paths in stages, adding relations to the appropriate path FilterRelations collection. I then build the filter expression and try to add it to the path's Relation.CustomFilter collection. I thought that by adding the relations to the FilterRelations, I was making all of the fields visible to the filter expression. However, when I run the code, I get a Null Reference Exception on the statement in which I attempt to add the expression to the filter. Here is the code:

            SuperbillsEntity superbill = new SuperbillsEntity(claimInfo.SuperbillID);
            DataAccessAdapter adapter = new DataAccessAdapter(connectionString);

            PrefetchPath2 path = new PrefetchPath2((int)EntityType.SuperbillsEntity);
        
            IPrefetchPathElement2 accountPath = path.Add(SuperbillsEntity.PrefetchPathAccount);
            accountPath.SubPath.Add(AccountsEntity.PrefetchPathChart).SubPath.Add(ChartsEntity.PrefetchPathPerson);

            IPrefetchPathElement2 accountDetailPath = accountPath.SubPath.Add(AccountsEntity.PrefetchPathAccountDetails);
            accountDetailPath.FilterRelations.Add(SuperbillsEntity.Relations.AccountsEntityUsingAccountId);
            accountDetailPath.FilterRelations.Add(AccountsEntity.Relations.AccountDetailsEntityUsingAccountId);

            PredicateExpression dateFilter = new PredicateExpression();
            dateFilter.Add(new FieldCompareExpressionPredicate(AccountDetailsFields.EffectiveDate, null, ComparisonOperator.LessEqual, new Expression(SuperbillsFields.ServiceDate)));
            accountDetailPath.Relation.CustomFilter.Add(dateFilter);

Everything compiles, but I get that Null Reference Exception on the last statement above. What is the right way to implement this kind of filtering?

Thanks, --BJ

BJ,

Your code gets the null reference exception because accountDetailPath.Relation.CustomFilter is null and you call the add method. Instead of doing that set that property to your date filter.

 public SuperbillsEntity GetSuperbillWithChildren(int id)
{
            SuperbillsEntity superbill = new SuperbillsEntity(id);
            //set up prefetch info
            PrefetchPath2 path = new PrefetchPath2((int)EntityType.SuperbillsEntity);

            IPrefetchPathElement2 accountPath = path.Add(SuperbillsEntity.PrefetchPathAccount);
            accountPath.SubPath.Add(AccountsEntity.PrefetchPathChart).SubPath.Add(ChartsEntity.PrefetchPathPerson);

            IPrefetchPathElement2 accountDetailPath = accountPath.SubPath.Add(AccountsEntity.PrefetchPathAccountDetails);
            accountDetailPath.FilterRelations.Add(SuperbillsEntity.Relations.AccountsEntityUsingAccountId);
            accountDetailPath.FilterRelations.Add(AccountsEntity.Relations.AccountDetailsEntityUsingAccountId);

            PredicateExpression dateFilter = new PredicateExpression();
            FieldCompareExpressionPredicate pred = new  FieldCompareExpressionPredicate(AccountDetailsFields.EffectiveDate, null, 
                                                               ComparisonOperator.LessEqual, 
                                                               new Expression(SuperbillsFields.ServiceDate));
            dateFilter.Add(pred);

            accountDetailPath.Relation.CustomFilter = dateFilter;
            using (DataAccessAdapter da = new DataAccessAdapter(_cs))
            {
                da.FetchEntity(superbill, path);
            }

            return superbill;
}

Thanks!

-Casey

Amazing! You're quite the genius.

Hmmm. Seems strrange that CustomFilter is null rather than an empty collection. That is one of the things I like about LLBLGen Entity classes -- child relations are never null, just empty.

--BJ