EntitySet Clear followed by Add results in duplicate entries

[txcraig]

I am seeing an issue similar to: http://forums.devart.com/viewtopic.php? ... set#p72059

>> if ...delete/add several items from EntitySet LinqConnect generates an Update statement for Entity A but only generates the Insert statements for EntitySet not the delete statements.

Below is my my code. I get the entity e_Policy, which has an EntitySet of e_ComputerGroup named ComputerGroups.

I have a business object which has the desired list of computer groups. My strategy to sync the EntitySet to the business object list is to .Clear() the EntitySet and then re-add each desired computer group.

However this results in duplicate entries (in cases where there should be an effective 'remove' of one of the elements), since no DELETE is ever generated to correspond with the .Clear() call. All that is generated is the UPDATE for the e_Policy and an INSERT for the EntitySet e_ComputerGroups.
I have used other ORMs and they support Clear() followed by re-adding the desired entries.

I could workaround this by manually calculating for each desired entry, whether it would result in an add or delete by comparing the current EntitySet list. Or I could SubmitChanges after calling Clear(), but that is not desirable because it creates a whole new set of problems by submitting changes midway through a possibly complex business transaction.

Can you advise if this behavior is a bug? It seems like an ORM should be able to handle the case of figuring out the SQL required to cause the database to match the ORM objects after simple manipulation of those ORM objects. Isn't this the whole point of an ORM?

Thanks
Craig

Code: Select all

    e_Policy pol = GetPolicyById(bo.IdGuid.ToString(), ctx);

    // sync the DAL from the business object
    pol.DateModified = DateTime.UtcNow;

    // Sync the EntitySet by clearing and re-adding desired entities from business object
    pol.e_ComputerGroups.Clear();
    foreach (var group in bo.ComputerGroups)
    {
        e_ComputerGroup groupToAdd = e_ComputerGroup.GetComputerGroupById(group.IdGuid.ToString(), ctx);
        pol.e_ComputerGroups.Add(groupToAdd);
    }
    ctx.SubmitChanges();

results in:

Begin transaction
Execute:
UPDATE public.policies SET date_modified = :p1 WHERE id = :key1;
INSERT INTO public.policy_to_computer_groups (computer_group_id, policy_id) VALUES (:p2, :p3);

Expected a DELETE to correspond to entries that were effectively removed as a result of the Clear() and re-add of EntitySet members.

[MariiaI]

Could you please send us a complete test project, which this issue could be reproduced and specify the following details:
- the version of LinqConnect;
- the DBMS you are working with;
- send us the DDL/DML scripts for the necessary database objects, etc.

Looking forward to your reply.

[txcraig]

OK I will prepare the test project and send it once it is ready. Thanks.

[MariiaI]

We are looking forward to your reply with a sample project for reproducing the issues.

[txcraig]

I have emailed in the sample project

[MariiaI]

Thank you for the test project. We will investigate it more clearly and contact you as soon as possible.

[txcraig]

Hello Craig,

We have investigated the issue you have reported. This is an expected behaviour. To resolve the issue, please use this code:
testPolicy.e_ComputerGroups.Clear();
ctx.SubmitChanges();

testPolicy.e_ComputerGroups.Add(testGroup);
ctx.SubmitChanges();

instead of this:
testPolicy.e_ComputerGroups.Clear();
testPolicy.e_ComputerGroups.Add(testGroup);
ctx.SubmitChanges();

Best regards,
Mariia
Devart Team

OK, this is unfortunate since as I mentioned, submitting mid-way through a complex business transaction causes all sorts of other problems for me. I will have to workaround this by manually syncing the ORM List to my business object by examining each element and deciding if it should be added or removed. This is the type of work that you would hope an ORM could do for you (i.e. developer can fetch an ORM object, manipulate it, and ORM figures out the SQL needed to make the DBMS match the manipulated ORM object).

[MariiaI]

Currently, there is no plans to change this behaviour. However, we will inform you if any changes regarding this are available.