Hi,

is it possible to use a unique constraint to define a one-to-many relationship between tables, instead of using the primary key. I have a table with a very bad PK, which i really can't use to define relations. Bad DB-design, i know, but this DB has been around for decades now, lots of code has been written around it, and the DBA isn't willing to change this PK.

It would be nice that at design-time we can specify our own PK (in case there is none present for example), as we can do with entities mapped on views. Or specify a UC instead of the PK.

If there is no PK on the table, you should be able to define one in the designer, though if there is already one, it's not possible to add another one of course.

relations on surrogate keys (UC's) isn't supported at the moment. I'll add it to the todo list, but that won't help you much NOW.

If the relational model is very bad, you could opt for setting up the same schema again (but without the data) and redefine PK's and FK's in that schema, then use that schema for your project. Then, point the generated code to the real database. It's a workaround, but could solve some problems.

I have a variation on this theme: I want to define a many-to-one relation between two tables using only a part of a field to join to the PK of the second table. e.g. Table 1 has a period field (YYYY-WW) and table 2 has a PK on Year.

Since there is another application (not .NET) referencing these tables that has problems with tables containing calculated fields i cannot use the obvious route of adding calculated fields to the tables.

Is there a way to use prefetch paths in this situation, or do I have to fetch the tables one after another?

wvnoort wrote:

I have a variation on this theme: I want to define a many-to-one relation between two tables using only a part of a field to join to the PK of the second table. e.g. Table 1 has a period field (YYYY-WW) and table 2 has a PK on Year.

Since there is another application (not .NET) referencing these tables that has problems with tables containing calculated fields i cannot use the obvious route of adding calculated fields to the tables.

Is there a way to use prefetch paths in this situation, or do I have to fetch the tables one after another?

There is a way in code, you can create your own EntityRelation object with the 2 fields and the code will then think they're related. Though there is a problem with the format, as the period field has a different format than the year PK.

With the EntityRelation in code, you can create your own PrefetchPathElement2 object, peek into the generated code's entity.PrefetchPathOtherEntity properties how to do it, it's pretty simple. Then simply add the node to a prefetch path and it should work. Again, if there aren't any issues with the format.