none
LINQ insert creating duplicates? RRS feed

  • Question

  • Hey Folks...

    I'm hoping someone can save my sanity ;)

    I'm doing what would seem to be a very simple insert of a new record in a table.  No matter what I do, by which I mean moving around when I call my datacontext, etc I'm winding up without fail, creating two records.  I'm working with 2 different operations, one is an update which works fine, than afterwards I'm creating a new record in another unrelated table, which is the one that's getting duplications.  The relevant code is:

    var createUserID = from o in dc.UserProfiles
                                where o.Email.ToLower() == usr.Email.ToLower()
                                select o;
                        createUserID.First().UserID = usr.ProviderUserKey.ToString();
                        dc.SubmitChanges();                   
                        Group newGroup = new Group();
                        newGroup.UserID = usr.ProviderUserKey.ToString();
                        newGroup.GroupName = "Friends";
                        dc.Groups.InsertOnSubmit(newGroup);
                        dc.SubmitChanges();

    I've been beating my head against the desk for hours over this, tried calling SubmitChanges only once for all the updates, tried multiple datacontexts, nothing seems to help...

    Hopefully someone can point me in the right direction.

    Thanks in advance!

    Saturday, February 11, 2012 12:31 AM

Answers

  • Hi PaulBinCT;

    As Alan_chen has stated there is a relationship between the two tables UserProfiles and Group and so they are not totally separate they have a relationship. This is why I used the Add method of the Groups collection in the UserProfiles table to add the new Group table entry.

    To your question, "BTW, I see you using an "add" method, which I've seen referenced in early LINQ tutorials, but I don't seem to have accessible to me... I assumed it's been replaced by InsertOnSubmit, am I wrong?", That is not correct. A collection has a Add method so that new objects can be added to the relationship.

    If you are still having issues with this and if you can post your complete project I will look at it. Just zip the complete project and place it on the .Net where I can download it. If you have a Windows Live account you can use your SkyDrive.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by PaulBinCT Monday, February 13, 2012 3:27 PM
    Monday, February 13, 2012 2:26 PM

All replies

  • Hi PaulBinCT;

    What exactly is being duplicated in the DB? In the first case you are modifying a record in one table and in the second you are creating a new record to insert into a completely different table.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, February 11, 2012 5:39 PM
  • Sorry for not being clear Fernando... the update works fine.  The insert is creating a second identical record in the intended table.  I have a temporary fix that frankly I don't understand why it works but it does.  In between the insert and the submit, I inserted a query to check for the presence of the record previously created and the subject of the insert command... if the query returns null (as of course it should) then it does the submit changes.  For some reason that I don't understand, that seems to have fixed it.  So, that's a "solution" but of course not a very satisfactory one :S

    Thanks for any advice!

    Saturday, February 11, 2012 8:32 PM
  • Hi PaulBinCT;

    That sounds strange. If you would like I can take a look at the problem but I would need a copy of the file DBMLFileName.Designer.cs to do so. If you would like me to have a look if you can zip that file up because it may be a little long and place it somewhere on the web where I may download it. If you have a Windows Live account you can place it on your SkyDrive and past the link here.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, February 11, 2012 10:00 PM
  • That's awfully nice of you Fernando, thanks!  If nothing else I'd love to know what's going on, even though I seem to have a workaround.  Hopefully this is what you need:

    https://skydrive.live.com/redir.aspx?cid=9a140dca8c6deb60&resid=9A140DCA8C6DEB60!177&parid=9A140DCA8C6DEB60!148&authkey=!AFkcnrw_W6AQaPs

    Again, thanks very much for the assistance, I'm grateful!

    Saturday, February 11, 2012 10:22 PM
  • Hi PaulBinCT;

    I think I was looking at this the wrong way when I first answered the question. From the code you posted and the Linq to SQL class there is an association between the two class and it looks like you just are adding a new Group to the UserProfiles. If that is the case then do the query modify the fields in that record and then create a new Group do NOT fill in the PK or the FK keys Linq will do that by itself. Then just add the new group to the UserProfiles.Groups collection and then save the record. See modified code below.

    // Get the first record in UserProfiles with the selected email address 
    var createUserID = (from o in dc.UserProfiles
                        where o.Email.ToLower() == usr.Email.ToLower()
                        select o).FirstOrDefault();
                        
    // If the above query found the UserProfiles then modeify and add
    // a new Group
    if( createUserID != null )
    {
        createUserID.UserID = usr.ProviderUserKey.ToString();
        // The FK in Group must be assigned Linq to SQL will take care of it
        Group newGroup = new Group();
        newGroup.GroupName = "Friends";
        // Assign the new Group to the UserProfiles Groups collection
        // Link to SQL will do the rest. 
        createUserID.Groups.Add(newGroup);
        // Now save changes.
        dc.SubmitChanges();
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, February 12, 2012 10:36 PM
  • Thanks for that Fernando... here's the "thing" that doesn't add up to me.  Maybe I posted an earlier iteration, but at the moment the "Groups" table is totally seperate, to make sure that there was no wierd unintended behaviours going on.  But no matter what I do (short of the aforementioned query injected between the insert and the update call) I wind up with 2 records.  Am I missing something?

    BTW, I see you using an "add" method, which I've seen referenced in early LINQ tutorials, but I don't seem to have accessible to me... I assumed it's been replaced by InsertOnSubmit, am I wrong? 

    And once again... thank you, I truly appreciate your help!

    Sunday, February 12, 2012 11:47 PM
  • Hi Paul,

    I think @Fernando may miswrite it. From the .cs file, there is a relationship between UserProfilers table and Groups table. I think you can track the T-SQL generated by LINQ to SQL: context.Log = Console.Out;

    BTW, thanks @Fernando for your support!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 13, 2012 7:36 AM
    Moderator
  • Hi PaulBinCT;

    As Alan_chen has stated there is a relationship between the two tables UserProfiles and Group and so they are not totally separate they have a relationship. This is why I used the Add method of the Groups collection in the UserProfiles table to add the new Group table entry.

    To your question, "BTW, I see you using an "add" method, which I've seen referenced in early LINQ tutorials, but I don't seem to have accessible to me... I assumed it's been replaced by InsertOnSubmit, am I wrong?", That is not correct. A collection has a Add method so that new objects can be added to the relationship.

    If you are still having issues with this and if you can post your complete project I will look at it. Just zip the complete project and place it on the .Net where I can download it. If you have a Windows Live account you can use your SkyDrive.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by PaulBinCT Monday, February 13, 2012 3:27 PM
    Monday, February 13, 2012 2:26 PM
  • Thanks very much Fernando and Alan...  I truly appreciate all the time you've both taken!

    Needless to say, I guess I need to review what I thought I knew ;)  Where I'm still befuddled is that if I open a new Linq class, import all the tables from scratch and manually delete the association between the two tables and then rebuild everything I assumed that in fact the Groups table would be "free standing" and clearly that's not the case.  Anyway, I'll hit the books so I don't make the same mistake again, thank you!

    Monday, February 13, 2012 3:26 PM
  • Hi PaulBinCT;

    If you do what you stated, "if I open a new Linq class, import all the tables from scratch and manually delete the association between the two tables and then rebuild everything I assumed that in fact the Groups table would be "free standing" and clearly that's not the case.", If you did this then you are correct that the Groups table would not have a relationship in the model in your program but in the file you uploaded this was not the case because the association and then entity set were still there in the two classes and the reason for my given solution. Also doing this in your model does not delete the association from the data base and it will still uphold any constraints that it may have.

    If you can post your project or create a sample project showing the issue I will have a look at it and try to find out what is happening. If you do upload the project please make sure to zip it and if you can include a SQL script to create the tables so I can run the app.

    .


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 13, 2012 4:21 PM