Hi,

If I want a dynamicList from a select of multiple tables, how do I do this?

For example, I have two tables without a real relation, but I would like to do this:

SELECT CUST.Name AS CuName, COMP.name AS CoName FROM CUSTOMER CUST, COMPANY COMP WHERE CUST.COLUMN1 = COMP.COLUMN1 AND CUST.COLUMN2 = COMP.COLUMN2 AND CUST.NO = 1000

I keep having two problems:

1: I only get FROM on one table, FROM CUSTOMER CUST 2: I can't get an alias on both tables in the where clause

DataTable dynamicList = new DataTable();
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Name ,0, "CuName", "CUST");
fields.DefineField(CompanyFieldIndex.Name ,0, "CoName", "COMP");

IPredicateExpression filter = new PredicateExpression();

filter.Add(new FieldCompareExpressionPredicate(EntityFieldFactory.Create(CustomerFieldIndex.Column1),
                ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(CompanyFieldIndex.Column1)), "CUST"));

filter.AddWithAnd(new FieldCompareExpressionPredicate(EntityFieldFactory.Create(CustomerFieldIndex.Column2),
                ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(CompanyFieldIndex.Column2)), "CUST"));

filter.AddWithAnd(PredicateFactory.CompareValue(CustomerFieldIndex.No, ComparisonOperator.Equal,1000, "CUST"));

TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, null, true, null, null, 0, 0);

1) either define the relation in the designer, regenerate code and you can add the relation to your dynamic list

or 2) create the EntityRelation object in code manually. This takes a few lines of code, for details how to do this, please check the generated RelationClasses' code.

You specify non-ansi joins. You should think in ansi joins instead, so a relation between customer and company on COLUMN1 and COLUMN2, will already add the 2 compares between these two columns to the query as these are part of the ON clause in the INNER JOIN, so you then just have to focus on the predicate left, Customer.No = 1000.

Ok, I have tried this now, but I have this problem:

"Relation at index X doesn't contain an entity already added to the FROM clause. Bad alias?"

Should I do something more?

Gr.,

Robin

relation = new EntityRelation(RelationType.ManyToMany);
            relation.StartEntityIsPkSide = false;               
            relation.AddEntityFieldPair(EntityFieldFactory.Create(CustomerFieldIndex.Column1), 
                EntityFieldFactory.Create(CompanyFieldIndex.Column1));
            relation.AddEntityFieldPair(EntityFieldFactory.Create(CustomerFieldIndex.Column2), 
                EntityFieldFactory.Create(CompanyFieldIndex.Column2));
            
relationsToUse.Add(relation, "CUST", "COMP", JoinHint.Inner);

OK...figured it out...changed order of entities in my new relation