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

Question

I found a bunch of possible duplicates around this but none really seemed to be having the same problem I'm having.

I'm getting a DuplicateKeyException: Cannot add an entity with a key that is already in use. Here's my SqlProductsRepository:

public class SqlProductsRepository : IProductsRepository
{
    private Table<Product> productsTable;
    private Table<Image> imagesTable;

    public SqlProductsRepository(string connectionString)
    {
        productsTable = (new DataContext(connectionString)).GetTable<Product>();
        imagesTable = (new DataContext(connectionString)).GetTable<Image>();

        // Populate all of the images for each product found
        foreach (Image image in imagesTable)
        {
            productsTable.Where<Product>(x => x.ProductID == image.ProductID).FirstOrDefault().Images.Add(image);
        }
    }

    public IQueryable<Product> Products
    {
        get { return productsTable; }
    }

    public IQueryable<Image> Images
    {
        get { return imagesTable; }
    }

    public void SaveProduct(Product product)
    {
        EnsureValid(product, "Name", "Description", "Category", "Price");

        if (product.ProductID == 0)
            productsTable.InsertOnSubmit(product);
        else
        {            
            productsTable.Attach(product);

            productsTable.Context.Refresh(RefreshMode.KeepCurrentValues, product);
        }

        productsTable.Context.SubmitChanges();
    }

The problem is somewhere around adding the imagesTable in. If I do this for a Product which has no images, there's no issue. It's only when a product has Images does this problem occur. Product and Image are pretty basic, as you would expect:

[Table(Name = "Images")]
public class Image
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int ImageID { get; set; }

    [Column] public int ProductID { get; set; }
    [Column] public int SortOrder { get; set; }
    [Column] public string Path { get; set; }
}

[Table(Name = "Products")]
public class Product : IDataErrorInfo
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync=AutoSync.OnInsert)]
    public int ProductID { get; set; }

    [Column] public string Name { get; set; }
    [Column] public string Description { get; set; }
    [Column] public decimal Price { get; set; }
    [Column] public string Category { get; set; }

    private IList<Image> _images;
    public IList<Image> Images {
        get
        {
            if (_images == null)
                _images = new List<Image>();
            return _images;
        }
        set
        {
            _images = value;
        }
    }

This problem is killing me. I've tried all sorts of variations, including getting the original product first and passing that in as the second parameter of Attach (in addition to just passing the "true" as a second parameter).

Does anyone know what could cause this?

Answer 1

First observation. Assuming based on tags that you're using LINQ to SQL, try using a single data context and LoadOptions. Something like (untested):

private Table<Product> productsTable;
private Table<Image> imagesTable;
private DataContext context;

public SqlProductsRepository(string connectionString)
{
    context = new DataContext(connectionString);
    var loadOptions = new DataLoadOptions();
    loadOptions.LoadWith<Product>(item => item.Image);
    context.LoadOptions = loadOptions;
    productsTable = context.GetTable<Product>();
}