Hello everyone,

I have a three tables/classes:

| A | | B | | C |

| A_ID | | B_ID | | | | etc... | | A_ID | | B_ID | | | | etc... | | AQQ | | | | | | FOO |

I need to fetch the data from the DB to the entity classes in the below structure:

A_entity-->B_entity-->C_entity

using PrefetchPath. I've done everything, but I have one issue with fetching a table C with some filtering.

The problem is that I want to filter the data in table C in the following rule:

select * 
from C
where AQQ = 5
or ((not exists 
(select *
from C c1
where c1.B_ID = B.B_ID
and AQQ = 5)) and AQQ is null)

Other words I've got a few doubled records in the C table. In case when for the B_ID record the AQQ with 5 is availible i want to fetch only this record. Otherwise i want to fetch only a record for which AQQ is null.

The problem is that I need to use the prefetchpaths and don't wanna return the related data - I want a Object in the hierarchy and I cannot create the aliases for the B table.

Could anyone give me some advices how can I hadle it?

Regards, Milosz Kubanski

I've tried the following filtering predicate but it doesn't work correctly - generated TSQL returns error The multi-part identifier "CEntity.B_ID" could not be bound

IPredicateExpression complicatedPredicate = new PredicateExpression((new PredicateExpression(CEntity.AQQ== 5)).AddWithOr(new PredicateExpression(new FieldCompareSetPredicate(CEntity.AQQ, null, CEntity.AQQ, null, SetOperator.In, new PredicateExpression(CEntity.B_ID == CEntity.B_ID), true)).AddWithAnd(CEntity.AQQ == DBNull.Value)));

Any ideas ?

Regards, MiloszeS

Hi,

Can you post the full code when you build de query and fetch ?

Please note that it is a pseudo code - the original is less clear then this one.

AEntity ret = new AEntity(someParameter);

IPrefetchPath2 path = new PrefetchPath2((int)EntityType.AEntity);

IPrefetchPath2 BPath = path.Add(AEntity.PrefetchPathB, 0, null, null, new SortExpression(BFields.SomeField | SortOperator.Ascending)).SubPath;

// IPredicateExpression complicatedPredicate = new PredicateExpression((new PredicateExpression(CEntity.AQQ== 5)).AddWithOr(new PredicateExpression(new FieldCompareSetPredicate(CEntity.AQQ, null, CEntity.AQQ, null, SetOperator.In, new PredicateExpression(CEntity.B_ID == CEntity.B_ID).AddWithAnd(CEntity.AQQ == 5), true)).AddWithAnd(CEntity.AQQ == DBNull.Value)));

IPrefetchPath2 svAnswerPath = BPath.Add(BEntity.PrefetchPathC, 0, complicatedPredicate, null, new SortExpression(CFields.SomeField | SortOperator.Ascending)).SubPath;

using (DataAccessAdapter adapter = new DataAccessAdapter()) { adapter.FetchEntity(ret, path); }

Hi,

You fetch an entity of type A with predicate expression on CEntity. You must use a RelationPredicateBucket to do that. That why you get this error i guess.

OK, now it works .

Thanks a lot . By the way, LLBLGen is amazing - great job.

Regards, MiloszeS