Hello, I have a question on defining relations in the LLBLGen designer (version 4.2)

Imagine working with a legacy database (SQL server) where there are no foreign keys defined between entities, but is instead using a concept of "functional keys". For instance, the database could have a table "Users" where each user has some fields, and the combination of firstname/lastname/dateofbirth is "unique" and used as a functional key. Then everywhere where the user is referenced, instead of using a foreing key UserId, the functional key is in place. So an Order entity would have a Userfirstname/userlastname/userdateofbirth to identify the user.

User

UserId Firstname LastName DateOfBirth Address etc...

Order

OrderId UserFirstName UserLastName UserDateOfBirth OrderNumber OrderQuantity etc...

Is it possible, in the LLBGen designer, to define a relation between UserEntity and OrderEntity, specifying the fields of the functional key as "foreign key" ? In that way that a UserEntity has a "Orders" property, and also a correct PrefetchPath is generated. All this WITHOUT changing the database schema ! (otherwise it would of course be simple to just add a UserId FK to the Orders table)

there are no foreign keys defined between entities, but is instead using a concept of "functional keys".

The question are these functional keys (composite key) is defined as a PK at the database level?

No, they are not, then I could have used the "Add normal relationship" in the designer.

Every table has a surrogate key "Id" which is the primary key.

There is a unique constraint on the functional key though.

This is currently not supported. We have plans to add it to a v5.x version but for now it's not supported. You have to define the relationship manually in the designer, which isn't great if there are a lot of relationships.

You have to define the relationship manually in the designer, which isn't great if there are a lot of relationships.

But how do I do it in the designer ? I only find the "Add normal relationship" and that screen will only work with the "real" PK.

HcD wrote:

You have to define the relationship manually in the designer, which isn't great if there are a lot of relationships.

But how do I do it in the designer ? I only find the "Add normal relationship" and that screen will only work with the "real" PK.

Yeah you define the UC based fields as the PK on the entity (by marking them identifying fields), and then you can define the relationship. In v4 this will give you the problem that DDL SQL is created as it now things you want to change the PK on the table. Ignore that. This is a bit tedious. In v5 we fixed that with the new sync model.

An alternative could be to setup a surrogate schema, which is an empty database with the same tables, but with the UC fields as the PK and the FKs on these fields. Create the project from that schema, and at runtime using catalog/schema overwriting switch to the actual db.

ok I'll give that a go

Problem with this I assume is that the "real" PK as defined then are gone, so other entities that DO use the Id as a Foreign key, no longer will have a navigator to the entity

Yeah I know, this schema is not the best I've ever worked with

With the changes in v5.x, both navigators will co-exist ?

The change we made in v5 is that schemas which are read from the DB are never modified unless you tell the designer to do so, so you can alter the model at will and these changes aren't resulting in relational model changes and ddl sql scripts.

Support for what you need isn't currently in v5.0, but as EF7 supports it now too, we'll look into adding it in a 5.x version.

I just had a (stupid or brilliant, the outcome will decide) idea :

What If I map the user twice, once normal to UserEntity with PK Id, and once UserWithFunctionalKeyEntity where I define the PK as the functional key fields. Then the UserWithFunctionKeyEntity should have an Orders navigator property, right ?

But the next hurdle is then a table UserDetails which does have the UserId as foreign key to a User. How to fetch a user with both the orders and the userdetails in one go ?

In the solution you posted, you can't fetch that graph in one go, as it's a disconnected graph.

Personally, I would resolve this using an intermediate table that correlates the Id and the other functional PK.

UserDetails.UserId -> User.Id
Order.(SomeX1, SomeX2) -> UserIntermediate.(SomeX1, SomeX2)
UserIntermediate.UserId -> User.Id

Just an idea, I don't know if that is feasible in your scenario.