Hi!, as with many, I've adopted an app & need to do maintenance work. I need to make a llblpro (2.6) prefetch query thingy for the SQL below.

I can get the 1st translation done. My issue is with the PRODUCTGROUP join for TD2.

I've looked at 'advanced filtering' but nothing jumps out as the answer.

All help is appreciated. N

DECLARE @LanguageId AS uniqueidentifier = '662A33B5-3B7D-DF11-AF91-000C298FB3A5'
    SELECT  P.[ProductName],
            CASE 
                WHEN TD1.[TranslatedText] IS NULL THEN P.[ProductName] 
                ELSE TD1.[TranslatedText] 
            END AS 'ProductName',
            PG.[ProductGroupCode],
            CASE 
                WHEN TD2.[TranslatedText] IS NULL THEN PG.[ProductGroupName] 
                ELSE TD2.[TranslatedText] 
            END AS 'ProductGroupName'
    FROM [dbo].[Product] P
    JOIN [dbo].[ProductGroup] PG ON P.ProductGroupID = PG.ProductGroupID
    LEFT OUTER JOIN [dbo].[TranslationDictionary] TD1 ON P.[ProductID]=TD1.[MessageID] AND TD1.[LanguageID]=@LanguageID AND TD1.[ColumnName]='PRODUCTNAME'
    LEFT OUTER JOIN [dbo].[TranslationDictionary] TD2 ON PG.[ProductGroupID]=TD2.[MessageID] AND TD2.[LanguageID]=@LanguageID AND TD2.[ColumnName]='PRODUCTGROUPNAME'

What have you tried yourself so far? for joining an entity multiple times, see: http://www.llblgen.com/Documentation/2.6/hh_goto.htm#Using%20the%20generated%20code/Adapter/Filtering%20and%20Sorting/gencode_filteringadvanced_adapter.htm#advancedfiltering (scroll to the bottom of the page)

Hi!,

I used the code below to get the TD1

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductEntity);

            // join product group
            prefetchPath.Add(ProductEntity.PrefetchPathProductGroup);


            // get translations for product
            IPredicateExpression productLangaugeFilter = new PredicateExpression(TranslationDictionaryFields.LanguageId == ISession.LanguageId);
            prefetchPath.Add(ProductEntity.PrefetchPathTranslationDictionary, 5, productLangaugeFilter);

and then linq to select either/or

var name = product.TranslationDictionary.Where(t => t.ColumnName == "PRODUCTNAME").Select(t => t.TranslatedText).FirstOrDefault() ?? product.ProductName;

I did have a look at the 'advanced filtering' you have linked to but, because I am not joining again to the **product **=, rather product group, the example wasn't particularly useful.

Many thanks. N

// join product group prefetchPath.Add(ProductEntity.PrefetchPathProductGroup);

I guess you are mixing prefetching related entities with Joining.

You don't need to use PrefetchPaths, just join to the entities needed. And you will uses aliases to join to TD1 and TD2.

Also, apparently you are fetching a couple of fields, so you will need to use a DynamicList. Then you have 2 options, the easier one is to fetch 4 fields, ProductName, ProductGroupName, TD1.TranslatedText & TD2.TranslatedText. Then follow up with your approach to set ProductName and ProductGroupName according to the other 2 fields at the client side, not in the database

The other option which is more complex is to use a DBFunctionCall to execute the CASE at the DB side, and only fetch those 2 fields.

Please let me know if you need any help with the code.

Hi, well, I have got so far with the dynamiclist; didn't find too much trouble with the dbfunctioncall.

// fields
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(ProductFields.ProductName, 0, "Name");
fields.DefineField(ProductFields.ProductCode, 1, "Code");
fields.DefineField(ProductGroupFields.ProductGroupName, 2, "Group");
fields.DefineField(TranslationDictionaryFields.TranslatedText, 3, "TName", "TD1");
fields.DefineField(TranslationDictionaryFields.ColumnName, 4, "TNameColumnName", "TD1");
fields.DefineField(TranslationDictionaryFields.TranslatedText, 5, "TGroupName", "TD2");
fields.DefineField(TranslationDictionaryFields.ColumnName, 6, "TGroupColumnName", "TD2");

fields[0].ExpressionToApply = new DbFunctionCall(
    "CASE {1} WHEN NULL THEN {0} ELSE {1} END", 
    new object[] { ProductFields.ProductName, fields[3] });
fields[2].ExpressionToApply = new DbFunctionCall(
    "CASE {1} WHEN NULL THEN {0} ELSE {1} END", 
    new object[] { ProductGroupFields.ProductGroupName, fields[5] });


// tables
IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(ProductEntity.Relations.ProductGroupEntityUsingProductGroupId, JoinHint.Left);
bucket.Relations.Add(ProductGroupEntity.Relations.TranslationDictionaryEntityUsingMessageId, "TD2", JoinHint.Left);
bucket.Relations.Add(ProductEntity.Relations.TranslationDictionaryEntityUsingMessageId, "TD1", JoinHint.Left);

System.Guid poland = new System.Guid("672A33B5-3B7D-DF11-AF91-000C298FB3A5");
System.Guid germany = new System.Guid("662A33B5-3B7D-DF11-AF91-000C298FB3A5");

bucket.PredicateExpression.Add((TranslationDictionaryFields.ColumnName.SetObjectAlias("TD1") == "PRODUCTNAME") &
(TranslationDictionaryFields.LanguageId.SetObjectAlias("TD1") == poland));

bucket.PredicateExpression.Add((TranslationDictionaryFields.ColumnName.SetObjectAlias("TD2") == "PRODUCTGROUPNAME") &
(TranslationDictionaryFields.LanguageId.SetObjectAlias("TD2") == poland));

// predicate    
PredicateExpression pred = new PredicateExpression();
pred.Add(ProductFields.ProductId == new System.Guid("2025bac7-3b7d-df11-af91-000c298fb3a5"));
bucket.PredicateExpression.Add(pred);


DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true);

It kinda works except the extra clauses for the TD1 & TD2 tables are in the WHERE clause rather than as part of the JOIN, so I get [code]SELECT [Hydra].[dbo].[Product].[ProductName] , CASE [LPA_T2].[TranslatedText] WHEN NULL THEN [Hydra].[dbo].[Product].[ProductName] ELSE [LPA_T2].[TranslatedText] END AS [Name] , [Hydra].[dbo].[Product].[ProductCode] AS

, [Hydra].[dbo].[ProductGroup].[ProductGroupName]
, CASE [LPA_T1].[TranslatedText]  WHEN NULL THEN [Hydra].[dbo].[ProductGroup].[ProductGroupName] ELSE [LPA_T1].[TranslatedText] END AS [Group]
, [LPA_T2].[TranslatedText] AS [TName]
, [LPA_T2].[ColumnName] AS [TNameColumnName]
, [LPA_T1].[TranslatedText] AS [TGroupName]
, [LPA_T1].[ColumnName] AS [TGroupColumnName] 
FROM 
[Hydra].[dbo].[Product]  
JOIN [Hydra].[dbo].[ProductGroup]  ON  [Hydra].[dbo].[ProductGroup].[ProductGroupID]=[Hydra].[dbo].[Product].[ProductGroupID]
LEFT JOIN [Hydra].[dbo].[TranslationDictionary] [LPA_T1]  ON  [Hydra].[dbo].[ProductGroup].[ProductGroupID]=[LPA_T1].[MessageID]
LEFT JOIN [Hydra].[dbo].[TranslationDictionary] [LPA_T2]  ON  [Hydra].[dbo].[Product].[ProductID]=[LPA_T2].[MessageID]
WHERE
 [LPA_T2].[ColumnName] = @ColumnName1 AND [LPA_T2].[LanguageID] = @LanguageId2 AND 
 [LPA_T1].[ColumnName] = @ColumnName3 AND [LPA_T1].[LanguageID] = @LanguageId4 AND 
 [Hydra].[dbo].[Product].[ProductID] = @ProductId5

which returns an empty set, rather than

[code]SELECT [Hydra].[dbo].[Product].[ProductName] , CASE [LPA_T2].[TranslatedText] WHEN NULL THEN [Hydra].[dbo].[Product].[ProductName] ELSE [LPA_T2].[TranslatedText] END AS [Name] , [Hydra].[dbo].[Product].[ProductCode] AS

, [Hydra].[dbo].[ProductGroup].[ProductGroupName]
, CASE [LPA_T1].[TranslatedText]  WHEN NULL THEN [Hydra].[dbo].[ProductGroup].[ProductGroupName] ELSE [LPA_T1].[TranslatedText] END AS [Group]
, [LPA_T2].[TranslatedText] AS [TName]
, [LPA_T2].[ColumnName] AS [TNameColumnName]
, [LPA_T1].[TranslatedText] AS [TGroupName]
, [LPA_T1].[ColumnName] AS [TGroupColumnName] 
FROM 
[Hydra].[dbo].[Product]  
JOIN [Hydra].[dbo].[ProductGroup]  ON  [Hydra].[dbo].[ProductGroup].[ProductGroupID]=[Hydra].[dbo].[Product].[ProductGroupID]
LEFT JOIN [Hydra].[dbo].[TranslationDictionary] [LPA_T1]  ON  [Hydra].[dbo].[ProductGroup].[ProductGroupID]=[LPA_T1].[MessageID] AND [LPA_T1].[ColumnName] = @ColumnName3 AND [LPA_T1].[LanguageID] = @LanguageId4
LEFT JOIN [Hydra].[dbo].[TranslationDictionary] [LPA_T2]  ON  [Hydra].[dbo].[Product].[ProductID]=[LPA_T2].[MessageID] AND [LPA_T2].[ColumnName] = @ColumnName1 AND [LPA_T2].[LanguageID] = @LanguageId2 
WHERE
 [Hydra].[dbo].[Product].[ProductID] = @ProductId5

which will return values.

Is this 'extra clauses on the join' facility available with llbl? If so, an example would be useful.

You can add the filter to the JOIN, by adding it to the CustomFilter property of the entityRelation.

e.g.

var firstRelation = ProductGroupEntity.Relations.TranslationDictionaryEntityUsingMessageId;
firstRelation.CustomFilter = new PredicateExpression(/*Use your extra filters*/);

bucket.Relations.Add(firstRelation, "TD2", JoinHint.Left);