One question related to LLBLGen 3.0 when using model first: Is there a way to model a "strong" relationship that would result in PK/FK relationship with CASCADE DELETE?

Thanks in advance

No. There is not thing like that. What do you need?

daelmo wrote:

No. There is not thing like that. What do you need?

Here is the scenario:

In model first, I model my entities (let's say a customer, orders and order details) and the relationships between entities

So there will be a relationship between order and order details I want in the model to specify that the relationship is an aggregation relation: when I delete an order, I want to automatically delete the order details, no confirmation required

So: from the model, the database schema should be generated with a relationship between order and orderdetails with CASCADE DELETE ON

Obviously the relationships can't by default be generated with Cascade delete, should be only when specified as so in model.

When using LLBLGEN in a database first scenario that was not a problem since you create the schema, thus can add cascade delete relationships

When using LLBLGEN in a full model first approach, we should have a way to model relationships that results to relationships with CASCADE DELETE

Makes sense?

I understand now. Unfortunately that is not supported. So you must add that constraint in database side.

We'll add a workitem for adding this to a future v3.x version. Thanks for the suggestion

Workaround today is to simply add them to the UPDATE DDL SQL script after it's generated, so edit the script after it's been generated and add the cascading options there.

Otis wrote:

We'll add a workitem for adding this to a future v3.x version. Thanks for the suggestion

Workaround today is to simply add them to the UPDATE DDL SQL script after it's generated, so edit the script after it's been generated and add the cascading options there.

Thanks for taking this feature into account, Yes for the workaround, however I am trying to automate things as far as possible, so integrating this feature in future version will be great!

Kind Regards

Just a follow up on this. Is this being addressed pretty soon?

This is especially needed when using Model first with entity framework and many to many relationships.

Can the DDL script at minimum recognize pure many to many detail tables? Since with EF, there is no other option to delete these records outside of using database CASCADING.

i.e.: tbl_User (id, username, email) tbl_Role(id, name) tbl_UserRole (user_id, role_id) <--- this should usually always get deleted

Would really nice to have this If I'm missing something, let me know.

MJC wrote:

Just a follow up on this. Is this being addressed pretty soon? ... Would really nice to have this If I'm missing something, let me know.

This was already added for EF and NHibernate. For EF: 1. Edit the involved entity. 2. Click at "Code gen info" subtab. 3. At Element combobox, select the involved navigator. 4. Check/Uncheck the "UseCascadeDeletes" setting.

Thanks. Didn't know about that flag.

It would be nice to have a flag for the generated SQL templates as well (for any framework project I would imagine). That way, in EF we wouldn't have to load all the objects into the context during delete, especially on pure many to many relationships, where it's a no-brainer.

Also, if there was a way to keep the designer in sync with the database as well to capture the DELETE constraints, and in a model first approach for us to be able to specify those where we desire, that would really be great. Then maybe the LLBL generated SQL would truly re-create the schema. Maybe v3.5 :-) This would be yet another advantage over using the EF designer in Visual Studio.

Thanks again.

MJC wrote:

Thanks. Didn't know about that flag. It would be nice to have a flag for the generated SQL templates as well (for any framework project I would imagine). That way, in EF we wouldn't have to load all the objects into the context during delete, especially on pure many to many relationships, where it's a no-brainer.

you mean, generating methods into the context which delete instances of entity X based on a filter? This isn't going to work without a SQL generation engine, as the predicate itself has to be generated to SQL, which can include joins, and the entity's mapping information has to be used to generate the target table reference to delete from. With a lot of entities, that's a lot of sql. When you use inheritance it's even more complicated. So I'm afraid we won't go that route.

Also, if there was a way to keep the designer in sync with the database as well to capture the DELETE constraints, and in a model first approach for us to be able to specify those where we desire, that would really be great. Then maybe the LLBL generated SQL would truly re-create the schema. Maybe v3.5 :-) This would be yet another advantage over using the EF designer in Visual Studio. Thanks again.

This is indeed a good suggestion: specify UPDATE/DELETE directives for foreign key constraints. We'll add this to our list of workitems for future versions. I can't promise in which version it will be added though. One warning for this feature: it's not always possible to define update/delete directives on FK's: when two direct paths exist between table A and B over FK constraints, it's not possible to define delete/update directives on all FKs on those paths as it could lead to inconsistent behavior: SQL Server for example will throw an error in some of these situations.

A workaround for today's version is that you add these directives on the FK constraints in the update DDL/SQL script, but it's indeed more work.