I have the following sql and I am having a problem creating a Dynamic TypedList...

SELECT TABLE_A.COLUMN_A,   
         TABLE_B.COLUMN_B  
    FROM TABLE_A, TABLE_B  
   WHERE ( TABLE_A.COLUMN_A = TABLE_B.COLUMN_A ) and  
         ( ( TABLE_A.COLUMN_B = ‘COLUMN_B’ ) AND  
         ( TABLE_B.COLUMN_NAME = 'COLUMN_A' ) )   
GROUP BY TABLE_A.COLUMN_A,   
         TABLE_B.COLUMN_B  
ORDER BY TABLE_B.COLUMN_B ASC   

Problem here is I am not able to create a custom relation between TABLE_B.COLUMN_A and TABLE_A.COLUMN_A, because there is no PK-FK relationship between these two tables. How can I create a custom relationship in code to do this OR what otherways can I accomplish this in LLBLGen?. Thanks.

I included a sample on creating a custom relation and using it on an entity fetch here, same thing can be done with a typed list.

We tend to define all our relationships and typed lists in code versus using the designer due to issues such as this, or maybe I have just been missing something obvious but we haven't been able to create custom joins in the designer that aren't primary keyed. Sadly 40% of our joins are non primary key so we have a lot of these defined in our business layers as reusable joins, this one isn't reusable but demonstrates how it works

EntityCollection Regions = new EntityCollection(new RegionEntityFactory());
// From dbo.Region R
// Inner join dbo.Branch B on B.RegionID = R.RegionID
// Inner join dbo.Lead L on L.BranchId = B.BranchID
// Where L.LeadID = @LeadID
IRelationPredicateBucket Bucket = new RelationPredicateBucket();

// define custom relation
IEntityRelation CustomRelation = new EntityRelation(RelationType.OneToOne);
CustomRelation.AddEntityFieldPair(EntityFieldFactory.Create(LeadFieldIndex.BranchID), 

EntityFieldFactory.Create(BranchFieldIndex.BranchID));
Bucket.Relations.Add(RegionEntity.Relations.BranchEntityUsingRegionID);

// add it to the relations collection
Bucket.Relations.Add(CustomRelation);

Bucket.PredicateExpression.Add(PredicateFactory.CompareValue(LeadFieldIndex.LeadID, ComparisonOperator.Equal, leadID));
// Do the lookup
Adapter.FetchEntityCollection(Regions, Bucket);

Thanks John.

You can specify now in 1.0.2004.2, if a table doesn't have a PK, to set it as PK so you can define a custom relation between two entities. Perhaps that solves some of your problems...

Thank you so much John. Here is my code snippet for anyone who will be in need for future reference...(by the way Frans could you please provided a simple code sample of creating PK in the code?)

            IDataAccessAdapter adapter = DataAccessAdapterFactory.GetDataAdapter();
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(TableAFieldIndex.ColumnA, 0, "ColumnA");
            fields.DefineField(TableBFieldIndex.ColumnB, 1, "ColumnB");

            //Add Relations
            IRelationPredicateBucket bucket = new RelationPredicateBucket();

            // define custom relation
            IEntityRelation CustomRelation = new EntityRelation(RelationType.OneToOne);
            CustomRelation.AddEntityFieldPair(EntityFieldFactory.Create(TableAFieldIndex.ColumnA),
                                              EntityFieldFactory.Create(TableBFieldIndex.ColumnA));

            bucket.PredicateExpression.Add(PredicateFactory.CompareValue(TableAFieldIndex.ColumnB, ComparisonOperator.Equal, sessionCode));
            bucket.PredicateExpression.Add(PredicateFactory.CompareValue(TableBFieldIndex.ColumnName, ComparisonOperator.Equal, strColumnName));

            // add it to the relations collection
            bucket.Relations.Add(CustomRelation);

            //Add Grouping
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);

            //Add Sorting
            ISortExpression sorter = new SortExpression(SortClauseFactory.Create(TableBFieldIndex.ColumnB, SortOperator.Ascending));

            DataTable dynamicList = new DataTable();
            try 
            {
                adapter.FetchTypedList(fields, dynamicList, bucket, 0, sorter, true, groupByClause);
            }
            catch (Exception ex)
            {
                throw new Exception( "There was a problem retrieving data from the TableA and TableB table: " + ex.Message.ToString(), ex.InnerException );
            }
            DataView dv = new DataView(dynamicList);
            return dv;

We do a lot of joins on tables that don't have physical FK's with 1800 tables and most of them related its a performance issue.

In addition we sometimes join unique or not so unique data to get statistics not using the primary key because for example a vendor sends us data and they don't know our 'uniqueid' etc. so we do a match on a different field than the PK. There are many other cases but thats a simple example.

Being able to define a relationship regarldess of using a primary key would be a dream, that is something I would setup and maintain in the designer.

John

Ganesh wrote:

Thank you so much John. Here is my code snippet for anyone who will be in need for future reference...(by the way Frans could you please provided a simple code sample of creating PK in the code?)

You should declare a PK in the designer, not in code. Though for an EntityRelation object, it's not important if a field which is marked as the 'PK side' is a real PK or not.

jtgooding wrote:

Being able to define a relationship regarldess of using a primary key would be a dream, that is something I would setup and maintain in the designer.

The main issue is: what type does E1.foo - E2.bar have? 1:1? m:n? unclear. For example as both sides aren't part of a UC nor PK, it's a m:n relation, as in: each E1 can have multiple E2's and vice versa. The construction of such a filter isn't standard for an m:n relation though: there's no intermediate entity, and what if the 2 sides use different typed fields? (as in: datepart(d, field) on one side and an int on the other side?...)

You probably won't agree because it has the potential to get more 'why doesn't this work' questions, but in my opinion it is the responsibility of the developer to know his data well enough to define the type of relationship in the data, and how the fields are bound together.

This is exactly what I am doing in code, I am selecting the relation type 1:M etc. and then defining the fields to join togther on, if the designer warned me on non compatible field types that would be great but again if I want to join something I should be able to do it just as I can do in raw SQL, the advantage of doing it in the designer is it would catch field changes that hardcoded relations in my business layer wouldn't.

John

You have a point in that relations should be definable using flexible manners, though the relations between random fields in random entities are not directly what you'd define on an entity: the relations defined are used to define how entities are related to eachother. So having entity A with a relation R to entity B, I can use A and R to get the related B's.

I then wonder... what could be an example of A, R and B for random relations? The only example I could think of was: A has a relation (m:1) with C over A.Foo = C.Foo B has a relation (m:1) with C over B.Foo = C.Foo

So now you could opt for a relation A-B which is m:n and which simply joins A.Foo with B.Foo, skipping C.

Stepping back I can only conclude that that would lead to wrong data, as the relation between A and B is defined through C, not by having the same field data in semantically the same fields. (IMHO)

Do you have a better example?