I refer to http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1748 which covers Prefetch with M:N relations as it relates to the 'manual' updating of such related entities/tables.

From the documentation it seems that to add new entities with related entities is really straight forward. Create a new entity instance, assign values to the field properties and add related entities to the Entity.Tbl* entities and then call the SaveEntity method on the Adapter. All related entities are updated and the relationships are managed so that the order records are created and the collection of auto generated primary key fields is done in the correct order.

In practise it seems a lot harder to do. I am running into fk insert conflicts because the SaveEntity does not appear to be saving the related entities in the correct order. I could obviously call SaveEntity on each entity in the correct order, but this seems like the wrong approach as this is precisely the work that should be left to the ORM libraries.

Could someone please provide some guidelines or references to posts which may help?

To illustrate the issue consider this scenario: Assume a m:n relationship represented by 3 tables A B and C: A has a 1:n relationship with B C has 1:n relationship with B B is thus the intermediate table

We have another table D A has a 1:n relationship with D D has a 1:n relationship with C

The resulting code LLBL generated code results in 4 entities: TblAEntity TblBEntity TblCEntity TblDEntity

Let's add a simple 1:n entity data collection and persist them to the datastore. This is a straightforward example and works as expected

    'Set some properties on a new enitityA instance - the '1' side of the 1:n
    entityA = New TblAEntity()
    entityA.AccountId = 1224
    entityA.AccountId = 1224

    'Create 2 instance of the related entity(TblD)  - the 'many' side of the 1:n
    entityD = New TblDEntity()
    entityD.MyName = "John"
    entityD.Email = "j@abc.com"
    entityA.TblD.Add(entityD)
    entityD = New TblDEntity()
    entityD.MyName = "Dave"
    entityD.Email = "d@abc.com"
    entityA.TblD.Add(entityD)

    'Save the entityA(1 record) and it's related TblD(2 records) is populated correctly
    adapter.SaveEntity(entityA)

Let's add new entities and include the m:n relations too and persist them to the datastore. This is where things get really tricky:

    'Set some properties on a new enitityA instance - the '1' side of the 1:n
    entityA = New TblAEntity()
    entityA.AccountId = 1224
    entityA.AccountName = "General"

    'Create 2 instances of the related entity(TblD)  - the 'many' side of the 1:n
    entityD = New TblDEntity()
    entityD.MyName = "John"
    entityD.Email = "j@abc.com"
    'Now add the related entity to TblA's collection
    entityA.TblD.Add(entityD)
    'Create another
    entityD = New TblDEntity()
    entityD.MyName = "Dave"
    entityD.Email = "d@abc.com"

    'Now add the related entity to TblA's collection
    entityA.TblD.Add(entityD)

    'Create m:n entities
    entityC = New TblCEntity()
    entityC.SomeField = "Some Value"

    'This is the intermediate table in the M:N relationship
    entityB = New TblBEntity()
    entityB.SomeProperty = "Some Value"
    
    'Add the related C entity
    entityB.TblC = entityC
    'Add the related A entity
    entityB.TblA = entityA

    'Save the entityA and a FK violation error is generated for TblC
    adapter.SaveEntity(entityA) 

Error

I did a SQL trace and it is apparent that the order of the inserts was as follows: TblA insert was attempted - this is as expected TblC insert was attempted, but failed as TblD had not been done first After this the transaction is aborted and of course TblB inserts will never happen.

So the question is, how to ensure that the order of inserts follows this order? TblA TblD TblC TblB

Additional Info: Runtime Lib version is 2.0.07.0611 Using LLBLGenPRO 2.0 Adapter model SQL Server Database

Just to make sure, I suppose you are using the recurse parameter of the Save method, right?

So the question is, how to ensure that the order of inserts follows this order? TblA TblD TblC TblB

• Please confirm that the relations between all the entities defined in LLBLGen Pro (nothing is hidden in the designer)
• You may try to save starting with entity D as follows: adapter.SaveEntity(entityD, false, true)
• Have you tried using a UnitOfWork?

Walaa wrote:

Just to make sure, I suppose you are using the recurse parameter of the Save method, right? - Please confirm that the relations between all the entities defined in LLBLGen Pro (nothing is hidden in the designer) - You may try to save starting with entity D as follows: adapter.SaveEntity(entityD, false, true)

Thanks for your prompt response. I'm not using the recurvise parameter explicitly as I understand that it is done by default.

From the documentation: Recursive saves are performed by default. This means that the DataAccessAdapter SaveEntity() logic will check whether included entities also have to be saved. In our examples above, this is not the case, but in your own code it can be. If you do not want this, you can specify 'false' for recursive saves in an overload of SaveEntity() in which case only the specified entity will be saved.

All the relations are defined in the designer as per the database schema.

• Have you tried using a UnitOfWork?

No I haven't. I'll look into that too, but I'd like to get something working with what I've got and if it is only a case of starting with the correct entity, I'll do it that way first.

I'll try the entityD save and report back on the outcome.

Success at last! I think I have come up with a rule of thumb to make this process easier. I'm happy to share it here:

Although the scenario I described is fairly common it is not entirely necessary to figure out the order to save entities absolutely if you have generated your code from a well designed database schema with all PK/FK relationships defined.

The generated code and ORM libraries are rather clever at figuring this out, provided you pay attention to a few important points:

1. For every new entity that you add, make sure that you set the related entity property where there is a foreign key defined. This is because the entity will only have this value once the parent(1) entity has been created in the database. This of course refers to schema where the primarykey is an identity column. Back to the example here

To illustrate the issue consider this scenario. It helps to sketch the relationships on paper for easier understanding: Assume a m:n relationship represented by 3 tables A B and C: A has a 1:n relationship with B C has 1:n relationship with B B is thus the intermediate table

We have another table D A has a 1:n relationship with D D has a 1:n relationship with C

The PK/FK relationships: A.pkAId to B.fkAId C.pkCId to B.fkCId A.pkAId to D.fkAId D.pkDId to C.fkDId

The resulting code LLBL generated code results in 4 entities: TblAEntity TblBEntity TblCEntity TblDEntity

The aim is to call SaveEntity on entityA in the code and save all related entities and ensure that the m:n(tables A,B,C) data is also added correctly. The good thing is that the SaveEntity call is transactional so it is all or nothing which is brilliant, because you don't put orphaned records into your database.

Example to apply point 1 to your entities: Ask - does my entityC have any foreign key relations? Yes it has D.pkDId to C.fkDId. This should be translated as entityC needs a reference to entityD and this becomes the code entityC.TblD = entityD

 'Set some properties on a new enitityA instance - the '1' side of the 1:n

    entityA = New TblAEntity()
    entityA.AccountId = 1224
    entityA.AccountName = "General"
   '*************************************************************
    'Apply point 1 above - what are the foreign keys for entityA?
    'None - no need to set entityA.Tbl*
    '*************************************************************

    'Create 2 instances of the related entity(TblD) - the 'many' side of the 1:n
    entityD = New TblDEntity()
    entityD.MyName = "John"
    entityD.Email = "j@abc.com"
    '*************************************************************
    'Apply point 1 above - what are the foreign keys for entityD?
    'A.pkAId to D.fkAId - so we need entityD to have a reference to entityA
    entityD.TblA = entityA
    'Now add the related entity to TblA's collection
    entityA.TblD.Add(entityD)
    '*************************************************************
    'Create another
    entityD = New TblDEntity()
    entityD.MyName = "Dave"
    entityD.Email = "d@abc.com"
    '*************************************************************
    'Apply point 1 above - what are the foreign keys for entityD?
    'A.pkAId to D.fkAId - so we need entityD to have a reference to entityA
    entityD.TblA = entityA
    'Now add the related entity to TblA's collection
    entityA.TblD.Add(entityD)
    '*************************************************************

    'Create m:n entities
    entityC = New TblCEntity()
    entityC.SomeField = "Some Value"
    '*************************************************************
    'Apply point 1 above - what are the foreign keys for entityC?
    'D.pkDId to C.fkDId - so we need entityC to have a reference to entityD
    entityC.TblD = entityD
    '*************************************************************

    'This is the intermediate table in the M:N relationship
    entityB = New TblBEntity()
    entityB.SomeProperty = "Some Value"
    
    '*************************************************************
    'Apply point 1 above - what are the foreign keys for entityB?
    'A.pkAId to B.fkAId -  so entityB needs a reference to entityA
    'Add the related A entity
    entityB.TblA = entityA
    'C.pkCId to B.fkCId - so entityB needs a reference to entityC
    'Add the related C entity
    entityB.TblC = entityC
    
    'Save the entityA
    adapter.SaveEntity(entityA) 

    'This single call would have updated all the correct database tables and added the data in the correct order

1. Use SQL Profiler to capture all the SQL generated. This really helps nail down the issue. If you omit a foreign key entity you will get the error to that effect in your code and the SQL will clearly show that the fkId field was not even set in the parameters. That is the biggest clue that you have missed out a related entity.

I hope this helps. Of course this is a rule of thumb which may not work in all scenarios, but I can't seem to go wrong with the cases I've tried and it has made my understanding of the SaveEntity process much clearer.

Thanks for the feedback.

Although using the following line:

   entityA.TblD.Add(entityD)

Should implecitly set the entityD.TblA

Walaa wrote:

Thanks for the feedback.

Although using the following line:

   entityA.TblD.Add(entityD)

Should implecitly set the entityD.TblA

Yes you are right here. Thank you for all your help on this. Post closed.