EntitySet Clear followed by Add results in duplicate entries

EntitySet Clear followed by Add results in duplicate entries

Postby txcraig » Thu 17 Sep 2015 17:15

I am seeing an issue similar to: http://forums.devart.com/viewtopic.php?f=31&t=21657&p=72059&hilit=entityset#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.
txcraig
 
Posts: 4
Joined: Wed 29 Apr 2015 15:18

Re: EntitySet Clear followed by Add results in duplicate entries

Postby MariiaI » Fri 18 Sep 2015 10:56

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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: EntitySet Clear followed by Add results in duplicate entries

Postby txcraig » Fri 18 Sep 2015 14:22

OK I will prepare the test project and send it once it is ready. Thanks.
txcraig
 
Posts: 4
Joined: Wed 29 Apr 2015 15:18

Re: EntitySet Clear followed by Add results in duplicate entries

Postby MariiaI » Mon 21 Sep 2015 06:15

We are looking forward to your reply with a sample project for reproducing the issues.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: EntitySet Clear followed by Add results in duplicate entries

Postby txcraig » Mon 28 Sep 2015 19:28

I have emailed in the sample project
txcraig
 
Posts: 4
Joined: Wed 29 Apr 2015 15:18

Re: EntitySet Clear followed by Add results in duplicate entries

Postby MariiaI » Wed 30 Sep 2015 09:53

Thank you for the test project. We will investigate it more clearly and contact you as soon as possible.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: EntitySet Clear followed by Add results in duplicate entries

Postby txcraig » Thu 15 Oct 2015 14:55

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).
txcraig
 
Posts: 4
Joined: Wed 29 Apr 2015 15:18

Re: EntitySet Clear followed by Add results in duplicate entries

Postby MariiaI » Fri 16 Oct 2015 09:06

Currently, there is no plans to change this behaviour. However, we will inform you if any changes regarding this are available.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)