I have 2 tables linked via a one-to-many unique constrained key on the primary table.

I can't use the primary key as it's a composite of two fields.

So table.Field = table2.Field where field is in a "Relationship"

When I drag the two tables into the SQL Enterprise Manager the link between the tables does appear

When I generate the code in the designer though, the relationship does not appear.

How can I get a LLBL relationship between these two tables when I can't use the primary key as the join.

Ian

Here is the relationship info from "exec sp_helpconstraint tb_catalogitems" It does appear that there is a correct unique constraint on the primary table.

UNIQUE (non-clustered) u_CatalogItems_SID

and is referenced in the child table:

Table is referenced by foreign key

MaxReTest.dbo.tb_claim_auth: FK_tb_claim_auth_tb_catalogitems

Am I missing something? Ian

You can create custom relations in code using the EntityRelation class.

To elaborate a bit on why you can't create the relation in the designer: one side has to be a PK. In a relational model you can join any attribute with any other attribute, though in an entity world you're dealing with entity a relates to entity b, which means one side has to be an identifying part.

Walaa wrote:

You can create custom relations in code using the EntityRelation class.

Hi,

I was unable to find an example of how to link two tables using the EntityRelation class in Help. Do you have one?

Otis wrote:

To elaborate a bit on why you can't create the relation in the designer: one side has to be a PK. In a relational model you can join any attribute with any other attribute, though in an entity world you're dealing with entity a relates to entity b, which means one side has to be an identifying part.

Most of our tables have a composite PK. FK tables then map to both these fields. As well some of the tables (ugh) don't have a formal PK/FK created (long story) as there are many orphans.

Recognizing that the data is not very clean and tiddy , what is the recommended strategy in generating/extending/using code so in the end I can get the typical Customer.Orders.OrderDetails kind of relationship I get from, for example, the NorthWinds DB example? Custom Properties that return OrderCollections that the property figures out with Predicates?

Thanks again for a fantastic tool. Our first real project is significantly ahead of schedule since we estimated it based on using Datasets etc.

Ian

You can check the reference manual to see how to use the EntityRelation class. And also if you search the forums you will find many examples.

what is the recommended strategy in generating/extending/using code so in the end I can get the typical Customer.Orders.OrderDetails kind of relationship I get from, for example, the NorthWinds DB example?

A simple trick to avoid all of those orphans and un-created physical relations, is to copy the schema of the current database into a new database create the physical relations that you want and generate the code according to this new schema. Then you can use this code against the old (current schema).