Using Self-Servicing and LLBLGenPro API...

When creating a derived table where the select statement uses fields from related entities, do I also need to specify the joins between them even though the entities understand it already?

My code works fine when I only use fields from one table, but as soon as I add a field from a related table, it doesn't automatically add the required join as I expected.

(I can try and post an example if the above isn't sufficient)

Depends on where you have that filter. Please post the code that fails.

daelmo wrote:

Depends on where you have that filter. Please post the code that fails.

This fails:

            DbUtils.ActualConnectionString = ConfigurationManager.ConnectionStrings["local"].ToString();
            var derivedTableFields = new ResultsetFields(4);
            derivedTableFields.DefineField(TopicTextFields.LanguageCode, 0);
            derivedTableFields.DefineField(TopicBaseFields.CountryCode, 1);
            derivedTableFields.DefineField(TopicBaseFields.ReportDate, 2);
            derivedTableFields.DefineField(TopicBaseFields.IDTopic, 3);
            derivedTableFields[2].AggregateFunctionToApply = AggregateFunction.Max;
            derivedTableFields[3].AggregateFunctionToApply = AggregateFunction.Max;

            var groupByClause = new GroupByCollection {derivedTableFields[0]};

            PredicateExpression filter = null;
            filter = new PredicateExpression(TopicTextFields.LanguageCode == "en");

            var dtDefinition = new DerivedTableDefinition(derivedTableFields, "OuterSelectAlias", filter, groupByClause);
            var relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                                               EntityType.TopicBaseEntity, "DerivedTableAlias",
                                               (new EntityField(TopicBaseFieldIndex.IDTopic.ToString(), "OuterSelectAlias",
                                                                typeof (string)) ==
                                                TopicBaseFields.IDTopic.SetObjectAlias("DerivedTableAlias")));
            var relations = new RelationCollection {relation};
            relations.SelectListAlias = "DerivedTableAlias";

            var topicBaseCollection = new TopicBaseCollection();
            topicBaseCollection.GetMulti(null, relations);

But this works:

            DbUtils.ActualConnectionString = ConfigurationManager.ConnectionStrings["local"].ToString();
            var derivedTableFields = new ResultsetFields(3);
            //derivedTableFields.DefineField(TopicTextFields.LanguageCode, 0);
            derivedTableFields.DefineField(TopicBaseFields.CountryCode, 0);
            derivedTableFields.DefineField(TopicBaseFields.ReportDate, 1);
            derivedTableFields.DefineField(TopicBaseFields.IDTopic, 2);
            derivedTableFields[1].AggregateFunctionToApply = AggregateFunction.Max;
            derivedTableFields[2].AggregateFunctionToApply = AggregateFunction.Max;

            var groupByClause = new GroupByCollection {derivedTableFields[0]};

            PredicateExpression filter = null;
            //filter = new PredicateExpression(TopicTextFields.LanguageCode == "en");

            var dtDefinition = new DerivedTableDefinition(derivedTableFields, "OuterSelectAlias", filter, groupByClause);
            var relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                                               EntityType.TopicBaseEntity, "DerivedTableAlias",
                                               (new EntityField(TopicBaseFieldIndex.IDTopic.ToString(), "OuterSelectAlias",
                                                                typeof (string)) ==
                                                TopicBaseFields.IDTopic.SetObjectAlias("DerivedTableAlias")));
            var relations = new RelationCollection {relation};
            relations.SelectListAlias = "DerivedTableAlias";

            var topicBaseCollection = new TopicBaseCollection();
            topicBaseCollection.GetMulti(null, relations);

HTH

Neil

You should look at the derived table (temp table), as a query of its won, isolated from the outsider query.

And thus you should supply a Join to fields from other tables used in this query. So please use the following overload of the DerivedTableDefinition cTor to pass the relation between TopicTextEntity and TopicBaseEntity:

public DerivedTableDefinition( 
   IEnumerable fields,
   string alias,
   IPredicateExpression filter,
   IRelationCollection relations,
   IGroupByCollection groupBy
)

Walaa wrote:

You should look at the derived table (temp table), as a query of its won, isolated from the outsider query.

And thus you should supply a Join to fields from other tables used in this query. So please use the following overload of the DerivedTableDefinition cTor to pass the relation between TopicTextEntity and TopicBaseEntity:

public DerivedTableDefinition( 
   IEnumerable fields,
   string alias,
   IPredicateExpression filter,
   IRelationCollection relations,
   IGroupByCollection groupBy
)

That works thanks. Although to get an appropriate overide for GetMulti I need to add a prefetch path. As I haven't understood (yet) prefetch paths and their use, I have arbitrarily added the TopicBaseEntity as the preftech path. I have supplied null or 0 for the other arguments I don't think I need. Wrong?

            var includeFieldsList = new IncludeFieldsList {TopicBaseFields.CountryCode, TopicBaseFields.IDTopic};
            var prefetchPath = new PrefetchPath((int) EntityType.TopicBaseEntity);
            var topicBaseCollection = new TopicBaseCollection();
            topicBaseCollection.GetMulti(null,3,null,outerRelations,prefetchPath,includeFieldsList,0,0);

I am also getting a not included field in the returned fields (TopicBaseFields.IDStatus, which I assumed would be excluded as it isn't mentioned in includeFileList).

neilx wrote:

That works thanks. Although to get an appropriate overide for GetMulti I need to add a prefetch path. As I haven't understood (yet) prefetch paths and their use, I have arbitrarily added the TopicBaseEntity as the preftech path. I have supplied null or 0 for the other arguments I don't think I need. Wrong?

Right.

neilx wrote:

I am also getting a not included field in the returned fields (TopicBaseFields.IDStatus, which I assumed would be excluded as it isn't mentioned in includeFileList).

Right.