Hi

I'm using LLBL 4.2 (final) on Win 7, VS 2013. I have a typed list where the tables couldn't be related within the designer because PRODUCT.SKU has 6 trailing zeros e.g. as per the below SQL in order to join correctly.

select SP.SKN, P.PRODUCT_NAME from SALES_PRODUCT_ROLLING_SUMMARY SP, PRODUCT P where SP.SKN + '000000' = P.SKU

I added a new field into the typedlist, and am trying to get the relation side correct - below is my attempt, which doesn't result in the extendedSknField being appended with the 6 zeros to my SalesProductRollingSummaryFields.SKN column during the join.

What am I missing?

                    private void BuildRelationSet()
        {
            _filterBucket.Relations.ObeyWeakRelations = base.ObeyWeakRelations;

            // __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations
            IEntityRelation relation = new EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.OneToOne, "Product" , true);

            var extendedSknField = new EntityField2(SalesProductRollingSummaryFields.SKN) { 
                ExpressionToApply = new DbFunctionCall("{0} + '000000'", new object[] { SalesProductRollingSummaryFields.SKN }) 
            };

            // relation.AddEntityFieldPair(SalesProductRollingSummaryFields.SKN, ProductFields.Sku);
            relation.AddEntityFieldPair(extendedSknField, ProductFields.Sku);
            relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("SalesProductRollingSummaryEntity", true);
            relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("ProductEntity", false);
            _filterBucket.Relations.Add(relation);
            // __LLBLGENPRO_USER_CODE_REGION_END
            
            OnRelationSetBuilt(_filterBucket.Relations);
        }

1- You shouldn't define new fields 2- Don't remove generated code as it will be re generated. 3- Inside the user code region, do as below, remove the relation, re-add it again, and this time set the CustomFilter, and make sure it replaces the ON clause.

Example:

Genetated Code

private void BuildRelationSet()
{
    _filterBucket.Relations.ObeyWeakRelations = base.ObeyWeakRelations;
    _filterBucket.Relations.Add(CategoryEntity.Relations.ProductEntityUsingCategoryId, "", "", JoinHint.Inner);
    _filterBucket.Relations.Add(SupplierEntity.Relations.ProductEntityUsingSupplierId, "", "", JoinHint.Inner);

    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations
    // __LLBLGENPRO_USER_CODE_REGION_END

    OnRelationSetBuilt(_filterBucket.Relations);
}

Modified Genetated Code

private void BuildRelationSet()
{
    _filterBucket.Relations.ObeyWeakRelations = base.ObeyWeakRelations;
    _filterBucket.Relations.Add(CategoryEntity.Relations.ProductEntityUsingCategoryId, "", "", JoinHint.Inner);
    _filterBucket.Relations.Add(SupplierEntity.Relations.ProductEntityUsingSupplierId, "", "", JoinHint.Inner);

    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations

    ((IList)_filterBucket.Relations).RemoveAt(1);

    var relation = SupplierEntity.Relations.ProductEntityUsingSupplierId;           
    
    relation.CustomFilter = new PredicateExpression(
        SupplierFields.SupplierId.SetExpression(new DbFunctionCall("{0} + '000000'", new object[] { SupplierFields.SupplierId }))
        == ProductFields.SupplierId);
    relation.CustomFilterReplacesOnClause = true;

    _filterBucket.Relations.Add(relation, "", "", JoinHint.Inner);
    
    // __LLBLGENPRO_USER_CODE_REGION_END

    OnRelationSetBuilt(_filterBucket.Relations);
}

Hi Walaa

That worked perfectly - thank you very much! Can I ask, is it also possible to fix the relationship within the relevant 2 entities to match too? I didn't notice a custom code area for this.

Cheers, James

Jamanga wrote:

Can I ask, is it also possible to fix the relationship within the relevant 2 entities to match too? I didn't notice a custom code area for this.

Yes, you can modify the ON clause using CustomFilter on the relations.