I am just curious how you can create a dynamic list at run time. Since the tables I will be selecting will not be known until user input, I won't know the entity type and fields to use until runtime. I could use several switch statements or ifs but there has to be a better way? I know which tables to join but the fields that are selected are dynamic. Here is my code so far:

IEntity myEntity = GeneralEntityFactory.Create(Enum.Parse(EntityType, "CountryGeoEntity",true));
        
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(myEntity.Fields["ID"].GetType(), 0, "CountryID", "ID");
            fields.DefineField(myEntity.Fields["Name"].GetType(), 0, "Name", "CountryName");
            fields.DefineField(PackFieldIndex.DataValue, 2, "PackData", "PackData", AggregateFunction.Sum);
            
            // joins
            relations = new RelationCollection();
            relations.Add(PackEntity.Relations.OutletEntityUsingOutletId, "Pack", "Outlet_Geo", JoinHint.Inner);
            relations.Add(PackEntity.Relations.ProductEntityUsingProdId, "Pack", "Product", JoinHint.Inner);
            relations.Add(ClientSelectedProductEntity.Relations.ProductEntityUsingId, "SelectedProd","Product", JoinHint.Inner);
            relations.Add(ClientProductGrpEntity.Relations.ClientSelectedProductEntityUsingProdGrpId, "ProdGroup", "SelectedProd", JoinHint.Inner);
            relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientProductGrpEntityUsingCpgId, "MarketGrpMember", "ProdGroup", JoinHint.Inner);
            relations.Add(ClientDefinedMarketEntity.Relations.ClientDefinedMarketProductMemberEntityUsingCdmId, "Market", "SelectedMrktProdGrps", JoinHint.Inner);
            relations.Add(TerritoryGeoEntity.Relations.TargetEntityUsingTerritoryId, "Target", "Outlet", JoinHint.Inner);
            relations.Add(TargetEntity.Relations.OutletEntityUsingOutletId, "Target", "Outlet", JoinHint.Inner);
            relations.Add(TargetEntity.Relations.ClientDefinedMarketEntityUsingClientDefMarketId, "Target", "Market");
            relations.Add(RegionGeoEntity.Relations.TerritoryGeoEntityUsingUpperGeoLevelId, "Region", "Territory", JoinHint.Inner);
            relations.Add(AreaGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, "Area", "Region", JoinHint.Inner);
            relations.Add(CountryGeoEntity.Relations.AreaGeoEntityUsingUpperGeoLevelId, "Country", "Area", JoinHint.Inner);
            
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
groupByClause.Add(fields[2]);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 0, 0);

ResultsetFields is just an EntityFields derived class, containing EntityField objects, the same as entity.Fields.

The dynamic list fetch routine just care about EntityFields objects, so if you know you have to retrieve 5 fields, you first create a 5 field fields object: ResultsetFields fields = new ResultsetFields[0];

Then, you have to define the actual fields. You can do that by picking fields from entities. For example: fields[0] = EntityFieldsFactory.CreateEntityFieldsObject(EntityType.CustomerEntity)["CompanyName"];

or: CustomerEntity c = new CustomerEntity(); fields[0] = c.Fields["CompanyName"];

this way you build up your resultset fields. For the groupby clause, just traverse the ResultsetFields object and add all the fields except perhaps one or two which have aggregate functions applied to them, that's up to you.

Thanks for the reply. When I try to generate the dynamic list I get the following error:

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias? What am I doing wrong and can anyone elaborate on the meaning of this error?

Thanks,

Vijay

Here is my code below:

// joins
            _relations = new RelationCollection();
            _relations.Add(TerritoryGeoEntity.Relations.TargetEntityUsingTerritoryId, "TerritoryGeo", "Target", JoinHint.Inner);
            _relations.Add(TerritoryGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, "TerritoryGeo", "RegionGeo", JoinHint.Inner);
            _relations.Add(TargetEntity.Relations.OutletEntityUsingOutletId, "Target", "Outlet_Geo", JoinHint.Inner);
            _relations.Add(TargetEntity.Relations.ClientDefinedMarketEntityUsingClientDefMarketId, "Target", "ClientDefinedMarket", JoinHint.Inner);
            _relations.Add(RegionGeoEntity.Relations.TerritoryGeoEntityUsingUpperGeoLevelId, "Region_Geo", "Territory_Geo", JoinHint.Inner);
            _relations.Add(AreaGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, "Area_Geo", "Region_Geo", JoinHint.Inner);
            _relations.Add(CountryGeoEntity.Relations.AreaGeoEntityUsingUpperGeoLevelId, "Country_Geo", "Area_Geo", JoinHint.Inner);
            _relations.Add(PackEntity.Relations.OutletEntityUsingOutletId, "Pack", "Outlet_Geo", JoinHint.Inner);
            _relations.Add(PackEntity.Relations.ProductEntityUsingProdId, "Pack", "Product", JoinHint.Inner);
            _relations.Add(ClientSelectedProductEntity.Relations.ProductEntityUsingId, "ClientSelectedProduct","Product", JoinHint.Inner);
            _relations.Add(ClientProductGrpEntity.Relations.ClientSelectedProductEntityUsingProdGrpId, "ClientProductGrp", "ClientSelectedProduct", JoinHint.Inner);
            _relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientProductGrpEntityUsingCpgId, "ClientDefinedMarketProductMember", "ClientProductGrp", JoinHint.Inner);
            _relations.Add(ClientDefinedMarketEntity.Relations.ClientDefinedMarketProductMemberEntityUsingCdmId, "ClientDefinedMarket", "ClientDefinedMarketProductMember", JoinHint.Inner);

This relation: _relations.Add(RegionGeoEntity.Relations.TerritoryGeoEntityUsingUpperGeoLevelId, "Region_Geo", "Territory_Geo", JoinHint.Inner);

defines 2 aliasses which aren't known yet. As it isn't the first relation in the pack, one of the 2 entities is already in the join list and the other will be added to the join list. However as both aliasses are not specified yet, the relation collection can't decide which one is already in the join list and which one to add.

See it as this: you join Customer, Order and OrderDetail You start with: Customer C INNER JOIN Order O ON ....

Then you'll specify Order O INNER JOIN OrderDetail OD ON ... As O is already in teh join list, this OD is added, and the list will become: Customer C INNER JOIN Order O ON .... INNER JOIN OrderDetail OD ON...

Though if I had specified it as this: Order O2 INNER JOIN OrderDetail OD ON ...

O2 isn't the same as O, so the join list can't be merged and an exception has to be thrown.

I think you either re-order the joins, or give RegionEntity the alias RegionGeo as that is already in the join list.

Here is my join statement in SQL that I would like to generate:

/*  
ClientDefinedMarket INNER JOIN
ClientProductGrp INNER JOIN
ClientSelectedProduct ON ClientProductGrp.ID = ClientSelectedProduct.ProdGrpID INNER JOIN
ClientDefinedMarketProductMember ON ClientProductGrp.ID = ClientDefinedMarketProductMember.CPG_ID ON 
ClientDefinedMarket.ID = ClientDefinedMarketProductMember.CDM_ID ON Product.ProdId = ClientSelectedProduct.SelProdID INNER JOIN
Territory_Geo INNER JOIN
Target ON Territory_Geo.ID = Target.TerritoryID INNER JOIN
Region_Geo ON Territory_Geo.UpperGeoLevelID = Region_Geo.ID INNER JOIN
Area_Geo ON Region_Geo.UpperGeoLevelID = Area_Geo.ID INNER JOIN
Outlet_Geo INNER JOIN
Pack ON Outlet_Geo.ID = Pack.OutletID ON Target.OutletID = Outlet_Geo.ID ON Product.ProdId = Pack.ProdID AND 
ClientDefinedMarket.ID = Target.ClientDefMarketID INNER JOIN
ZipCode_Geo ON Outlet_Geo.ZipCode = ZipCode_Geo.ID INNER JOIN
Country_Geo ON Area_Geo.UpperGeoLevelID = Country_Geo.ID
*/


relations = new RelationCollection();
_relations.Add(ClientDefinedMarketEntity.Relations.ClientDefinedMarketProductMemberEntityUsingCdmId, JoinHint.Inner);
_relations.Add(ProductEntity.Relations.ClientSelectedProductEntityUsingId, JoinHint.Inner);
_relations.Add(ClientProductGrpEntity.Relations.ClientSelectedProductEntityUsingProdGrpId, JoinHint.Inner);
_relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientProductGrpEntityUsingCpgId, JoinHint.Inner);
_relations.Add(TerritoryGeoEntity.Relations.TargetEntityUsingTerritoryId, JoinHint.Inner);
_relations.Add(RegionGeoEntity.Relations.TerritoryGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);
_relations.Add(AreaGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);
_relations.Add(OutletGeoEntity.Relations.PackEntityUsingOutletId, JoinHint.Inner);
_relations.Add(OutletGeoEntity.Relations.TargetEntityUsingOutletId, JoinHint.Inner);
_relations.Add(PackEntity.Relations.ProductEntityUsingProdId, JoinHint.Inner);
_relations.Add(ClientDefinedMarketEntity.Relations.TargetEntityUsingClientDefMarketId, JoinHint.Inner);
_relations.Add(ZipCodeGeoEntity.Relations.OutletGeoEntityUsingZipCode, JoinHint.Inner);     _relations.Add(CountryGeoEntity.Relations.AreaGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);

I can't seem to understand the ordering. Frans thanks for all the help. I appreciate it.

I think I was able to fix the previous error. Here is what I get now though:

Tables or functions 'dbo.Target' and 'dbo.Target' have the same exposed names. Use correlation names to distinguish them.

What does that error mean and is there anyway to output the generated SQL statement when it throws this exception?

            /*  
                FROM         Product, ClientDefinedMarket, ClientProductGrp, ClientDefinedMarketProductMember, ClientSelectedProduct, 
                        Territory_Geo, Target, Region_Geo, Area_Geo, Outlet_Geo, Pack, ZipCode_Geo, Country_Geo

                WHERE       Pack.ProdID = Product.ProdID AND Pack.OutletID = Outlet_Geo.ID AND Product.ProdID = ClientSelectedProduct.SelProdID
                        AND ClientSelectedProduct.ProdGrpID = ClientProductGrp.ID AND ClientProductGrp.ID = ClientDefinedMarketProductMember.CPG_ID
                        AND ClientDefinedMarketProductMember.CDM_ID = ClientDefinedMarket.ID AND Outlet_Geo.ID = Target.OutletID AND 
                        Outlet_Geo.ZipCode = ZipCode_Geo.ID AND
                        Target.ClientDefMarketID = ClientDefinedMarket.ID AND Target.TerritoryID = Territory_Geo.ID AND 
                        Territory_Geo.UpperGeoLevelID = Region_Geo.ID AND Region_Geo.UpperGeoLevelID = Area_Geo.ID AND 
                        Area_Geo.UpperGeoLevelID = Country_Geo.ID
            */
            _relations = new RelationCollection();
            _relations.Add(PackEntity.Relations.OutletGeoEntityUsingOutletId, JoinHint.Inner);
            _relations.Add(PackEntity.Relations.ProductEntityUsingProdId, JoinHint.Inner);
            _relations.Add(ProductEntity.Relations.ClientSelectedProductEntityUsingId, JoinHint.Inner);
            _relations.Add(ClientSelectedProductEntity.Relations.ClientProductGrpEntityUsingProdGrpId, JoinHint.Inner);
            _relations.Add(ClientProductGrpEntity.Relations.ClientDefinedMarketProductMemberEntityUsingCpgId, JoinHint.Inner);
            _relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientDefinedMarketEntityUsingCdmId, JoinHint.Inner);
            _relations.Add(OutletGeoEntity.Relations.TargetEntityUsingOutletId, JoinHint.Inner);
            _relations.Add(OutletGeoEntity.Relations.ZipCodeGeoEntityUsingZipCode, JoinHint.Inner);
            _relations.Add(TargetEntity.Relations.ClientDefinedMarketEntityUsingClientDefMarketId, JoinHint.Inner);
            _relations.Add(TargetEntity.Relations.TerritoryGeoEntityUsingTerritoryId, JoinHint.Inner);
            _relations.Add(TerritoryGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);
            _relations.Add(RegionGeoEntity.Relations.AreaGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);
            _relations.Add(AreaGeoEntity.Relations.CountryGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);
            

remove: _relations.Add(TargetEntity.Relations.ClientDefinedMarketEntityUsingClientDefMarketId, JoinHint.Inner);

as both of the entities in this relation are already in the join list: target is included via: _relations.Add(OutletGeoEntity.Relations.TargetEntityUsingOutletId, JoinHint.Inner); and ClientDefinedMarket is included via: _relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientDefinedMarketEntityUsingCdmId, JoinHint.Inner);

if you need the extra filter, specify it in a predicateexpression. As you specify every entity once, you don't need to alias and you also don't need to specify a joinhint as Inner is the default (JoinHint.None is resulting in an Inner join)

below I've added the entity names to each relation added to show you which entity is added to the join list and why.

// adds both, joinlist is empty.
_relations.Add(PackEntity.Relations.OutletGeoEntityUsingOutletId, JoinHint.Inner);

// adds Product, as Pack is already in the joinlist
_relations.Add(PackEntity.Relations.ProductEntityUsingProdId, JoinHint.Inner);

// adds ClientSelectedProduct as Product is already in the joinlist
_relations.Add(ProductEntity.Relations.ClientSelectedProductEntityUsingId, JoinHint.Inner);

// adds ClientProductGrp as ClientSelectedProduct is already in the joinlist
_relations.Add(ClientSelectedProductEntity.Relations.ClientProductGrpEntityUsingProdGrpId, JoinHint.Inner);

// adds ClientDefinedMarketProductMember as ClientProductGrp is already in the joinlist
_relations.Add(ClientProductGrpEntity.Relations.ClientDefinedMarketProductMemberEntityUsingCpgId, JoinHint.Inner);

// adds ClientDefinedMarket as ClientDefinedMarketProductMember is already in the joinlist
_relations.Add(ClientDefinedMarketProductMemberEntity.Relations.ClientDefinedMarketEntityUsingCdmId, JoinHint.Inner);

// adds Target as OutletGeo is already in the joinlist
_relations.Add(OutletGeoEntity.Relations.TargetEntityUsingOutletId, JoinHint.Inner);

// adds ZipCodeGeo as OutletGeo is already in the joinlist
_relations.Add(OutletGeoEntity.Relations.ZipCodeGeoEntityUsingZipCode, JoinHint.Inner);

// *** This following line gives an error as both are already in the join list. 
// *** So this line has to be removed
// _relations.Add(TargetEntity.Relations.ClientDefinedMarketEntityUsingClientDefMarketId, JoinHint.Inner);

// adds TerritoryGeo as Target is already in the join list
_relations.Add(TargetEntity.Relations.TerritoryGeoEntityUsingTerritoryId, JoinHint.Inner);

// adds RegionGeo as TerritoryGeo is already in the join list
_relations.Add(TerritoryGeoEntity.Relations.RegionGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);

// adds AreaGeo as RegionGeo is already in the join list
_relations.Add(RegionGeoEntity.Relations.AreaGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);

// adds CountryGeo as AreaGeo is already in the join list
_relations.Add(AreaGeoEntity.Relations.CountryGeoEntityUsingUpperGeoLevelId, JoinHint.Inner);

Thanks Frans. I really am starting to enjoy LLBLGen. Saves me so much time.

VijayB wrote:

Thanks Frans. I really am starting to enjoy LLBLGen. Saves me so much time.

Glad it's solved and you like LLBLGen Pro, VijayB

I'm trying to resolve a similar issue. Frans, you said:

if you need the extra filter, specify it in a predicateexpression.

I'm not sure how to do that, because my join is on two fields, not on a field and it's value. Here's the SQL query I'm trying to write as LLBLGen code. I get the "Tables or functions...have the same exposed names. Use correlation names to distinguish them." error (SQL server 2000):

SELECT yadaya...
FROM dbo.SearchCriteria 
  INNER JOIN dbo.SurveyItem ON dbo.SearchCriteria.ItemID = dbo.SurveyItem.ItemID 
  INNER JOIN dbo.SurveyItemSurveyPageMap ON dbo.SurveyItem.ItemID = dbo.SurveyItemSurveyPageMap.ItemID 
  INNER JOIN dbo.SurveyPage ON dbo.SurveyItemSurveyPageMap.PageID = dbo.SurveyPage.PageID 
  INNER JOIN dbo.SurveyForm ON dbo.SearchCriteria.FormID = dbo.SurveyForm.FormID 
    AND dbo.SurveyPage.FormID = dbo.SurveyForm.FormID
WHERE (dbo.SearchCriteria.SearchID = 79)            

and the LLBLGen code I'm trying to use:

IRelationPredicateBucket surveyItemFilter = new RelationPredicateBucket();
surveyItemFilter.Relations.Add(SurveyItemEntity.Relations.SearchCriteriaEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyItemSurveyPageMapEntity.Relations.SurveyItemEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyPageEntity.Relations.SurveyItemSurveyPageMapEntityUsingPageID);
surveyItemFilter.Relations.Add(SurveyFormEntity.Relations.SearchCriteriaEntityUsingFormID);
surveyItemFilter.Relations.Add(SurveyFormEntity.Relations.SurveyPageEntityUsingFormID);
surveyItemFilter.PredicateExpression.Add(PredicateFactory.CompareValue(SearchCriteriaFieldIndex.SearchID, ComparisonOperator.Equal, searchID));

I'm guessing it's the SurveyPageEntity.Relations... and ...Relations.SurveyPageEntityUsingFormID creating two instances of SurveyPage in the FROM part of the SQL.

Any help?

Thx, Jack

Jackk100 wrote:

I'm trying to resolve a similar issue. Frans, you said:

if you need the extra filter, specify it in a predicateexpression.

I'm not sure how to do that, because my join is on two fields, not on a field and it's value. Here's the SQL query I'm trying to write as LLBLGen code. I get the "Tables or functions...have the same exposed names. Use correlation names to distinguish them." error (SQL server 2000):

SELECT yadaya...
FROM dbo.SearchCriteria 
  INNER JOIN dbo.SurveyItem ON dbo.SearchCriteria.ItemID = dbo.SurveyItem.ItemID 
  INNER JOIN dbo.SurveyItemSurveyPageMap ON dbo.SurveyItem.ItemID = dbo.SurveyItemSurveyPageMap.ItemID 
  INNER JOIN dbo.SurveyPage ON dbo.SurveyItemSurveyPageMap.PageID = dbo.SurveyPage.PageID 
  INNER JOIN dbo.SurveyForm ON dbo.SearchCriteria.FormID = dbo.SurveyForm.FormID 
    AND dbo.SurveyPage.FormID = dbo.SurveyForm.FormID
WHERE (dbo.SearchCriteria.SearchID = 79)            

and the LLBLGen code I'm trying to use:

IRelationPredicateBucket surveyItemFilter = new RelationPredicateBucket();
surveyItemFilter.Relations.Add(SurveyItemEntity.Relations.SearchCriteriaEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyItemSurveyPageMapEntity.Relations.SurveyItemEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyPageEntity.Relations.SurveyItemSurveyPageMapEntityUsingPageID);
surveyItemFilter.Relations.Add(SurveyFormEntity.Relations.SearchCriteriaEntityUsingFormID);
surveyItemFilter.Relations.Add(SurveyFormEntity.Relations.SurveyPageEntityUsingFormID);
surveyItemFilter.PredicateExpression.Add(PredicateFactory.CompareValue(SearchCriteriaFieldIndex.SearchID, ComparisonOperator.Equal, searchID));

I'm guessing it's the SurveyPageEntity.Relations... and ...Relations.SurveyPageEntityUsingFormID creating two instances of SurveyPage in the FROM part of the SQL.

The relations you're using don't match the joins you defined in the SQL. I think the following code is more appropriate. (you don't join a table twice in your SQL, so you don't have to do that with relations either). You need an extra predicate in the last join clause. You can add that using the CustomFilter property of the entity relation as I show below.

// Define the extra filter for the SurveyForm join
IPredicateExpression formJoinFilter = new PredicateExpression();
formJoinFilter.Add(PredicateFactory.CompareExpression(SurveyPageFieldIndex.FormID, ComparisonOperator.Equal, 
    new Expression(EntityFieldFactory.Create(SurveyFormFieldIndex.FormID))));

// build filter with relation set.
IRelationPredicateBucket surveyItemFilter = new RelationPredicateBucket();
surveyItemFilter.Relations.Add(SearchCriteriaEntity.Relations.SurveyItemEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyItemEntity.Relations.SurveyItemSurveyPageMapEntityUsingItemID);
surveyItemFilter.Relations.Add(SurveyItemSurveyPageMapEntity.Relations.SurveyPageEntityUsingPageID);
surveyItemFilter.Relations.Add(SurveyPageEntity.Relations.SurveyCriteriaEntityUsingFormID).CustomFilter = formJoinFilter;
surveyItemFilter.PredicateExpression.Add(PredicateFactory.CompareValue(SearchCriteriaFieldIndex.SearchID, ComparisonOperator.Equal, searchID));

See "Custom filters for EntityRelations" in 'Using the generated code / Selfservicing - adapter/ Filtering and sorting'