Hi, i've been messing around getting the following sql query into llblgen, but to no avail yet.

SELECT
    *
FROM
    Store s RIGHT JOIN RewriterRule rr
        ON (rr.StoreId = s.Id OR rr.StoreId IS NULL)

the rewriter rule has a foreign key to store, but the foreign key can be null. what the query does is it fetches all the stores and their associated rewriterrules PLUS all the rewriter rules that have no foreign key relations for each store. what i've got so far in llblgen is this (using adapter)...

EntityCollection<StoreEntity> storeEntities = new EntityCollection<StoreEntity>(new StoreEntityFactory());

IRelationPredicateBucket storeRelationPredicateBucket = new RelationPredicateBucket();

EntityRelation entityRelation = new EntityRelation();
PredicateExpression predicateExpression = new PredicateExpression();
predicateExpression.AddWithOr(new FieldCompareNullPredicate(RewriterRuleFields.StoreId, null));

entityRelation.AddEntityFieldPair(StoreFields.Id, RewriterRuleFields.StoreId);
entityRelation.CustomFilter = predicateExpression;
entityRelation.CustomFilterReplacesOnClause = false;

storeRelationPredicateBucket.Relations.Add(entityRelation);

IPrefetchPath2 storePrefetchPath = new PrefetchPath2((int)EntityType.StoreEntity);
storePrefetchPath.Add(StoreEntity.PrefetchPathRewriterRule);

adapter.FetchEntityCollection(storeEntities, storeRelationPredicateBucket, storePrefetchPath);

the current solution returns 0 stores, although there should be 4 of them. any help would be appreciated. I'm on SQL server 2005 and llblgen 2.5 on .net 2.0.

thnx, Mihkel

now that i think about it, it's not even a matter of using right join, but just the defaul join (inner) with additional ON condition. i did the following assignment...

storeRelationPredicateBucket.Relations.ObeyWeakRelations = true;

this resulted in retruning the correct number of stores and the associated rewriterrules, but leaving out the ones with no FK relations.

You are mainly fetching Stores. And you are using a prefetchPath to fetch their related reWriterRules.

There is no plcae for reWriterRules referencing no Stores.

If you want to get all re-writerRules, then perhaps you want to fetch Re-writerRules as your main entities, and then use a prefetchPath to fetch their stores.

i guess i just have to make an aditional call to fetch the rewriterrules for each store, as the whole stores fetching method is one call which prefetches about 25 different associated entities all at once and it's quite difficult to patch in the rewriter rules into the same call. I'll probably leave it like that.

thanks for the help, Mihkel