Cannot add an entity with a key that is already in use.

Question

I'm having a bit of trouble trying to add an object to the database using LINQ to SQL.

I have three tables in my database:

--------------------------------------------
tblShows
--------------------------------------------
ShowID  |  Name
--------------------------------------------


--------------------------------------------
tblShowEvents
--------------------------------------------
ShowEventID  |  ShowID  |  Name
--------------------------------------------


--------------------------------------------
tblShowEventAttendees
--------------------------------------------
ShowEventAttendeeID  |  ShowEventID  |  Name
--------------------------------------------

A Show has many Events, and each Event has many Attendees.

I have a page where I'm creating a Show. On the page, I'm adding Events to the Show, and Attendees to each Event, and then saving the show all at once.

Here is my code:

Show show = new Show();
show.Name = txtName.Text.Trim();

//add ShowEvents to Show
//showEventsList is a variable containing a List of ShowEvents
foreach (ShowEvent ev in showEventsList)
{
    show.ShowEvents.Add(new ShowEvent
    {
        ShowID = show.ShowID,
        Name = ev.Name
    });
}

//Add ShowEventAttendees to each ShowEvent
//attendeesList is a variable containing a List of ShowEventAttendees
foreach (ShowEvent ev in show.ShowEvents)
{
    foreach (ShowEventAttendee attendee in attendeesList)
    {
        ev.ShowEventAttendees.Add(new ShowEventAttendee
        {
            ShowEventID = ev.ShowEventID,
            Name = attendee.Name
        });
    }
}

AppDataContext.DataContext.Shows.InsertOnSubmit(show);
AppDataContext.DataContext.SubmitChanges();

The issue occurs in the second foreach loop. If I take it out, the Show and ShowEvents get added to the database correctly.

What's the issue here?

EDIT for Jay:

I've tried this now:

AppDataContext.DataContext.Shows.InsertOnSubmit(show);
AppDataContext.DataContext.SubmitChanges();

//Add ShowEventAttendees to each ShowEvent
//attendeesList is a variable containing a List of ShowEventAttendees
foreach (ShowEvent ev in show.ShowEvents)
{
    foreach (ShowEventAttendee attendee in attendeesList)
    {
        ev.ShowEventAttendees.Add(new ShowEventAttendee
        {
            ShowEventID = ev.ShowEventID,
            Name = attendee.Name
        });
    }
}

AppDataContext.DataContext.SubmitChanges();  //trying to update, error here

But I get the same error on that last line. Going through the debugger, ShowEventID is no longer 0, but I'm getting the same error.

Answer 1

This is more of a database design issue. You should let the database handle data entry ID assignments.

If you are running Sql Server, go to your table design view, right click on the ID column and make this your primary key. Now look at the column properties. Set Identity Specification to yes. You can set the ID seed and Increment to whatever you want.

Now, when you run your updates, don't update the ID column, just the other data fields. Let the database worry about ID assignments.

When you enter a ShowEvent, have your Sql Method return a scalar value using "SELECT @@IDENTITY;" that represents the ID of the newly entered ShowEvent. Now, you can use this ID value to enter your Attendees, matching them to your ShowEvent ID. Make sure you perform the same Primary Key setup that I mentioned for your EventAttendees table. That way you are sure all of your attendees ID's are unique when they match up to the ShowEvent ID. Again, you shouldn't have to explicitly enter the Attendee ID. You should only have to enter the Foreign key value for the ShowEvent and match it to the Attendee data.

Not sure if that's clear, but if you need me to, I'll try to come up with some code.

-Matt

Answer 2

When you call ShowEventID = ev.ShowEventID, it is always 0 because no ID has been assigned.

Since you are probably using identity fields as your primary key identifiers, you'll have to save the show (with its ShowEvents) in order to have IDs assigned to those on those ShowEvents.

After that, the second loop will work.

Alternatively, if you store a reference to the actual ShowEvent object in ShowEventAttendee, (an association in LINQ-to-SQL) instead of using the integral identifier, you would call ShowEvent = ev and you could save everything at once.

update

Something is off. Every time you loop you are adding attendees to the same ShowEvent, a local variable called showEvent. I would have assumed the intent here to be adding attendees for each of the ShowEvents of show, so should showEvent be replaced with ev?