In the databases I get to work with the data is highly normalized (duplicated everywhere). For instance the way you would typically refer to another table is by PK (primary key), in the databases I have you get a PK reference and a text ID and a text Name field. This I assume makes everything so much easier for reporting purposes.

What I would like to do is make the code easier to handle. When I change (for instance, just a contrived example to show the difficulty and what I hope to achieve) an Order to point to a different Customer record I need to change the PK, ID and Name stored in the Order record. This is a giant pain since it can be easy to accidentally miss something when coding and then you get hard to find bugs that don't show up until weeks or years later.

Also LLBLGen has these nice entity relationships. Which makes it even easier to forget to change the other related fields. It is so easy to just do:

order.Customer = changedCustomer;

And you end up forgetting that it only really updates the PK, not the ID & Name.

What I would like to do is integrate LLBLGen's fine entity relationship stuff with some type of automatic updates so when the PK gets changed the ID and Name follow suit without needing to do it manually.

I use the Adapter model so I guess it would only really happen upon a save or something like that.

So I guess what I'm asking is:

• Where is the best place to manage the automatic updating (unfortunately the database is off limits, no triggers )?
• Is there some way to make it even more automatic, like can LLBL generate the code to do that for me?
• Is there something I'm missing that would make my life even better still? (maybe reflection or something magical?) Some (heck a lot) of the entities have dozens of relationships and there are a ton of entities in the database. I would like to automate as much as possible, or some generic class, anything to keep me from writing hundreds and hundreds of mind numbing lines of boilerplate code please!!! Especially since boilerplate code is even easier to forget to update when new relationships are created (which happens a lot lately [building the model] but afterwards will happen about every 9 months).

Where is the best place to manage the automatic updating (unfortunately the database is off limits, no triggers )?

I'd have used the Validation framework, to set the non-FK field prior to save. And this can be done once in one validator that's injected for any kind of entity.

Is there some way to make it even more automatic, like can LLBL generate the code to do that for me?

You can modify the templates to Synchronize the Name field at the same place the FK-PK synchronization takes place.

Both of the above 2 suggestions would highly depend on the naming convention of the Name field in the Primary Entity, and in The ForignKey-side Entity.

But this is going to be very tricky, say you have an EmailEntity, which has the following 2 FKs: FromUserId, and ToUserId, both refer to the User Entity.

Now if you have something like User.Name, and now you want to populate EmailEntity.FromUserName, and EmailEntity.ToUserName. If at the code side, you have the Email.FromUser.Name, how would you relate it to Email.FromUserName field. What I want to say is how the generic code will know that the Name of the related FromUserEntity, should be synchronized with the local FromUserName field. Since no relation is ties them both.

Is there something I'm missing that would make my life even better still? (maybe reflection or something magical?)

Yes, the name field must be added to the PK fields (composite PK). And thus the synchronization would be generated into code for you.

If you can't modify the database schema, you can at least modify a dummy copy of it, upon which the entities should be mapped. That's the fastest solution I can think of, to avoid any code manipulation.

Changing the templates should be manageable once I figure out how. The naming conventions are fairly standardized.

This last thing here:

Yes, the name field must be added to the PK fields (composite PK). And thus the synchronization would be generated into code for you.

If you can't modify the database schema, you can at least modify a dummy copy of it, upon which the entities should be mapped.

Sounds very interesting. I'm not sure I follow exactly what it is you are doing though. Could you expand upon that?

I do have a guess though and I want to ensure that this suggestion would work in the case (occasional) that the Name field doesn't actually match back to the other table (a legacy data thing)

I think Walaa is suggesting that you have a copy of the database in which the Name fields are added to the PKs on the tables, so that the generated code will update them for you.

The point he was making was that the structure of this database would then not actually match the structure of the production database.

Walaa will be back along later so I'm sure will expand on this further if I have misread what he meant.

Matt

That's what I meant.

You can modify the schema (a copy of the production schema), to add the Name field to the PK, to have both Id and Name as a composite PK. Then on the related tables, use both fields as FKs.

The only side effect of this is that using the modified generated code will force you to use both field when constructing the entity.

i.e.

var customer = new Customer(id, name);

rather than

customer = new Customer(id);

So if at some position in code you only know the id and you want to fetch the entity from the database, you'll have to fetch a collection while specifying a filter with the id. Rather than fetching an entity constructed with the id.

Bottom line, now you have 2 main approaches. 1- Using a Validator 2- Modify the schema

The first approach can be easier, if you may support a validator for each entity, or if you can bare to code few lines of code in one generic validator to witch in the entity type, and hence cast the entity passed to the validator to be able to access its fileds and related entities, to manually synchronize the required field.

If you are not into writing more lines of code, then use the second approach but on the expense of maintaining 2 versions of the schema, one for production and another for developement. Also you might need to write an extra line of code here or there to avoid instatiating an entity with the id alone.

Hope these suggestions made sense to you.