Why do I get this exception? {An item with the same key has already been added."})

Question

Aknittel
NewSellerID is the result of a lookup on tblSellers. These tables (tblSellerListings and tblSellers) are not "officially" joined with a foreign key relationship, either in the model or in the database, but I want some referential integrity maintained for the future. So my issue remains. Why do I get the exception ({"An item with the same key has already been added."}) with this code, if I don't begin each iteration of the foreach loop with a new ObjectContext and end it with SaveChanges, which I think will affect performance. Also, could you tell me why ORCSolutionsDataService.tblSellerListings (An ADO.NET DataServices/WCF object is not IDisposable, like LINQ to Entities??

==============================================

// Add listings to previous seller
int NewSellerID = 0;

// Look up existing Seller key using SellerUniqueEBAYID
var qryCurrentSeller = from s in service.tblSellers
        where s.SellerEBAYUserID == SellerUserID
        select s;

foreach (var s in qryCurrentSeller)
 NewSellerID = s.SellerID;

// Save the selected listings for this seller

foreach (DataGridViewRow dgr in dgvRows)
{

 ORCSolutionsDataService.tblSellerListings NewSellerListing = new ORCSolutionsDataService.tblSellerListings();
 NewSellerListing.ItemID = dgr.Cells["txtSellerItemID"].Value.ToString();
 NewSellerListing.Title = dgr.Cells["txtSellerItemTitle"].Value.ToString();
 NewSellerListing.CurrentPrice = Convert.ToDecimal(dgr.Cells["txtSellerItemPrice"].Value);
 NewSellerListing.QuantitySold = Convert.ToInt32(dgr.Cells["txtSellerItemSold"].Value);
 NewSellerListing.EndTime = Convert.ToDateTime(dgr.Cells["txtSellerItemEnds"].Value);
 NewSellerListing.CategoryName = dgr.Cells["txtSellerItemCategory"].Value.ToString();
 NewSellerListing.ExtendedPrice = Convert.ToDecimal(dgr.Cells["txtExtendedReceipts"].Value);
 NewSellerListing.RetrievedDtime = Convert.ToDateTime(dtSellerDataRetrieved.ToString());
 NewSellerListing.SellerID = NewSellerID;

 service.AddTotblSellerListings(NewSellerListing);

}

service.SaveChanges();

}

catch (Exception ex) { MessageBox.Show("Unable to add a new case. Exception: " + ex.Message);

}

Answer 1

It looks like you are creating a new object based on the old values, specifying the ID, and saving it to the DB. If that is true, then the new object will have the same ID as the one it is copied from. Are you updating, or saving a new record?

It also looks like there might be a much faster way to accomplish the same thing. If you can get the list of IDs that are being reassigned or copied, you could use SQL similar to:

update tblSellerListings set SellerID = "new seller id" where ItemID in (list of ids);

Re-using the context may be an issue if it is keeping around the incrementing ID from query to the next. Creating a new one would clear that value. I bet, if this is the case, that there is a method that will also clear that value.

Jacob

Answer 2

Jacob...thanks.

I am saving a new row. I am specificlly trying to avoid querying tblSellerListings and getting a list of ItemID's, which could be large. I want to do that logic at the server in a stored procedure. There is actually an insert Stored Procedure mapped to tblSellerListings, which ensures that the insert happens ONLY when a previous ItemID does NIT exist to save on the round trip for a lookup.

It turns out IF I just fill in the Primary Key for the new listing in the foreach loop with any monotonically increasing value, I can perform the savechanges OUTSIDE the foreach loop. The autogenerate key for ListingID (the primary key for tblSellerListings), still overrides this fake key assignment at the client.

Is this the fastest, easiest and least round-trip way to do this?