LINQ to SQL in ASP.NET MVC results in DuplicateKeyException

Question

I'm following the Sports Store example in Pro ASP.NET MVC Framework and I'm getting an exception related to LINQ that I cannot figure out. The full code is available through the website but here is a snippet to convey the problem.

private Table<Product> productsTable;
// ...
public void SaveProduct(Product product) {
  if (product.ProductID == 0)
    productsTable.InsertOnSubmit(product);
  else {
    productsTable.Attach(product);
    productsTable.Context.Refresh(RefreshMode.KeepCurrentValues, product);
  }
  productsTable.Context.SubmitChanges();
}

In the UI, I update an existing product and click save and the controller handles the post by calling SaveProduct(product) - where product is passed via parameter. A DuplicateKeyException is thrown upon attaching product. In the debugger, the product parameter is initialized and has an ID of 2.

I don't expect an exact answer but I'm hoping that someone can give me some hints as to where I can look to address this problem.

UPDATE: The following code works, but I'm still hoping to get the attach method above working.

public void SaveProduct(Product product) {
  if (product.ProductID == 0)
    productsTable.InsertOnSubmit(product);
  else {
    Product p2 = productsTable.Single(em => em.ProductID == product.ProductID);
    p2.Name = product.Name;
    p2.Description = product.Description;
    p2.Price = product.Price;
    p2.Category = product.Category;
  }
  productsTable.Context.SubmitChanges();
}

Answer 1

I may be wrong but I think the only time you can get this is if you fill an object and then try to save it. If you are updaing an existing object then I think you need to load it first, make the changes using something like UpdateModel and then save.

L2S then knows that the object it is saving has been loaded and that it's an update operation rather than an insert.

Does this make sense to your problem?

Answer 2

I figured it out but I'm giving the answer to griegs as he put me on the right track. This is here in case anyone else hits it. I used SQL Profiler and noticed that I wasn't seeing any hits to the database on save, not even to select any records.

I eventually came across a miniscule typo in my web.config that I didn't see in Beyond Compare due to line differences...

<component id="ProdsRepository" service="DomainModel.Abstract.IProductsRepository, DomainModel" type="DomainModel.Concrete.SqlProductsRepository, DomainModel" lifesyle="PerWebRequest">

vs.

<component id="ProdsRepository" service="DomainModel.Abstract.IProductsRepository, DomainModel" type="DomainModel.Concrete.SqlProductsRepository, DomainModel" lifestyle="PerWebRequest">

In case you don't spot it, the lifestyle attribute was misspelled. The repository was persisting - thus the data was already loaded as suggested by griegs.

Answer 3

You can change UpdateCheck property in your objects to Never (it makes Attach to work) or use somthing like this:

public void SaveProduct(Product product) 
{
  if (product.ProductID == 0)
    productsTable.InsertOnSubmit(product);
  else 
  {
   Product old = productsTable.Single(em => em.ProductID == product.ProductID);
   ApplyChanges(ref old, product);
  }
  productsTable.Context.SubmitChanges();
}

private static void ApplyChanges<T>(ref T Original, T Changes)
{
    Type OriginalType = typeof(T);

    PropertyInfo[] Info = OriginalType.GetProperties();

    foreach (PropertyInfo PI in Info)
    {
        foreach (Attribute attr in Attribute.GetCustomAttributes(PI)) 
        {
            // Check for the Column attribute.
            if (attr.GetType() == typeof(System.Data.Linq.Mapping.ColumnAttribute))
            {
                if(((attr as System.Data.Linq.Mapping.ColumnAttribute).IsDbGenerated == false) && PI.CanWrite)
                {
                    PI.SetValue(Original, PI.GetValue(Changes, null), null);
                }
                break;
            }
        }
    }
 }

Answer 4

I also got this when I had the ProductID (the key) in the view, which was there because I allowed VS to generate the view with the "View content : Edit" option. (The code in the book has the ProductID removed, but, I find that the best way to learn is to experiment a bit - and, although this error took a couple of hours, it was worth it).

BTW. Great book!