Good evening,

I'm having trouble with adding relationships to a RelationPredicateBucket. I'm using LLBLGen Pro 1.0.2005.1 Final.

I have a table structure something like this: tblPerson (table) -> PostalCityId (field) -> StreetCityId -- Both of these fileds foreign keys that are linked to the CityId field in the tblCity table

tblCity -> CityId -> CityName

tblCityRegion -> CityId -> RegionId -- Intermediate table is required here because City:Region relationship is m:n

tblRegion -> RegionId -> RegionName

Now, what I am trying to accomplish is getting a collection of PersonEntities based on a region. It should return all PersonEntities that have either a PostalCity or StreetCity in the region.

I have something like this so far....

RelationPredicateBucket rpb = new RelationPredicateBucket();
rpb.Relations.Add(PersonEntity.Relations.CityEntityUsingPostalCityId, "PostalCity");
rpb.Relations.Add(PersonEntity.Relations.CityEntityUsingStreetCityId, "StreetCity");
rpb.Relations.Add(CityEntity.Relations.CityRegionEntityUsingCityId, "PostalCity", "PostalRegion", JoinHint.None);
rpb.Relations.Add(CityEntity.Relations.CityRegionEntityUsingCityId, "StreetCity", "StreetRegion", JoinHint.None);

rpb.PredicateExpression.Add(PredicateFactory.CompareValue(CityRegionFieldIndex.RegionId, ComparisonOperator.Equal, Convert.ToInt16(entCriteria.SubCriteria), "PostalRegion"));                           rpb.PredicateExpression.Add(PredicateFactory.CompareValue(CityRegionFieldIndex.RegionId, ComparisonOperator.Equal, Convert.ToInt16(entCriteria.SubCriteria), "StreetRegion"));

Is this the right way to go about it...? I'm not getting any entities back at all.

There are other PredicateExpressions in the bucket but I have simplified it for you. And as you can probably see, if the code did work it would only return PersonEntities that had a PostalCity AND a StreetCity in the region specified. But as I say, it's not returning any at all.

Thanks Charles

I think you should try to build the following SQL query:

SELECT * from tblPerson 
WHERE PostalCityId IN (SELECT CityId FROM tblCityRegion WHERE RegionId = xx)
OR StreetCityId IN (SELECT CityId FROM tblCityRegion WHERE RegionId = xx)

To implement the IN predicate, use FieldCompareSetPredicate. Please refer to the LLBLGen Pro manual "Using the generated code -> Adapter / SelfServicing -> Filtering and sorting -> The predicate system"

I have fixed the issue by changing the JoinHint.None to JoinHint.Left and also using a .AddWithOr...

Here is the behaving code:

rpb.Relations.Add(PersonEntity.Relations.CityEntityUsingPostalCityId, "PostalCity"); rpb.Relations.Add(PersonEntity.Relations.CityEntityUsingStreetCityId, "StreetCity"); rpb.Relations.Add(CityEntity.Relations.CityRegionEntityUsingCityId, "PostalCity", "PostalRegion", JoinHint.Left); rpb.Relations.Add(CityEntity.Relations.CityRegionEntityUsingCityId, "StreetCity", "StreetRegion", JoinHint.Left);

rpb.PredicateExpression.Add(PredicateFactory.CompareValue(CityRegionFieldIndex.RegionId, ComparisonOperator.Equal, Convert.ToInt16(entCriteria.SubCriteria), "PostalRegion")).AddWithOr(PredicateFactory.CompareValue(CityRegionFieldIndex.RegionId, ComparisonOperator.Equal, Convert.ToInt16(entCriteria.SubCriteria), "StreetRegion"));