Batch Insert - Foreign Key Not Working

Question

I'm trying to do a batch insert and it's not working. I thought I had this working but something seems to have broken and I'd appreciate it if someone could show me what.

Edit - Here's the database schema:

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([Id])
)

CREATE TABLE [dbo].[ProductTopSellersCategory](
    [ProductId] [int] NOT NULL,
    [CategoryId] [int] NOT NULL,
    [Order] [int] NOT NULL,
    CONSTRAINT [PK_ProductTopSellersCategory]
          PRIMARY KEY CLUSTERED ([ProductId], [CategoryId])
)

ALTER TABLE [dbo].[ProductTopSellersCategory] ADD
    CONSTRAINT [FK_ProductTopSellersCategory_Products]
        FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Products] ([Id]),
    CONSTRAINT [FK_ProductTopSellersCategory_Categories]
        FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Categories] ([Id])

I have the following entities:

public class Category {
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

public class ProductTopSellerCategory {
    public virtual ProductTopSellerCategoryIdentifier Id { get; set; }

    private Product _product;
    public virtual Product Product {
        get { return _product; }
        set { _product = value; Id.ProductId = _product.Id; }
    }

    private Category _category;
    public virtual Category Category {
        get { return _category; }
        set { _category = value; Id.CategoryId = _category.Id; }
    }

    [Required]
    public virtual int Order { get; set; }

    public ProductTopSellerCategory() {
        Id = new ProductTopSellerCategoryIdentifier();
    }
}

[Serializable]
public class ProductTopSellerCategoryIdentifier {
    public virtual int ProductId { get; set; }
    public virtual int CategoryId { get; set; }

    #region Composite Id Members

    public override bool Equals(object obj) {
        if (obj == null || !(obj is ProductTopSellerCategoryIdentifier))
            return false;

        var i = (ProductTopSellerCategoryIdentifier)obj;

        return ProductId == i.ProductId && CategoryId == i.CategoryId;
    }

    public override int GetHashCode() {
        return ToString().GetHashCode();
    }

    public override string ToString() {
        return ProductId + "|" + CategoryId;
    }

    #endregion
}

With the corresponding fluent mappings:

public class CategoryMap : ClassMap<Category> {
    public CategoryMap() {
        Table("Categories");
        Id(x => x.Id);
        Map(x => x.Name);
    }
}

public class ProductTopSellerCategoryMap : ClassMap<ProductTopSellerCategory> {
    public ProductTopSellerCategoryMap() {
        Table("ProductTopSellersCategory");
        CompositeId(x => x.Id)
            .KeyProperty(x => x.ProductId)
            .KeyProperty(x => x.CategoryId);
        References(x => x.Product).ReadOnly();
        References(x => x.Category).ReadOnly();
        Map(x => x.Order, "[Order]");
    }
}

Now when I say:

var category = new Category() { Name = "Test 1" };
var product = session.Get<Product>(1);
var topSeller = new ProductTopSellerCategory() { Product = product, Category = category };

session.SaveOrUpdate(category);

session.SaveOrUpdate(topSeller);

session.Transaction.Commit();

It throws the error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProductTopSellersCategory_Categories". The conflict occurred in database "xxx", table "dbo.Categories", column 'Id'. The statement has been terminated.

I've tried to simplify this example as much as possible. I'd really appreciate the help. Thanks

Where did the foreign key come from? Did you create it by hand or was it generated by Nhibernate? if you created it yourself, please post the DDL for the key. — TedOnTheNet, Jul 19 '13 at 10:36

score 0 · Answer 1 · answered Jul 20 '13 at 13:30

0

You have a one-to-many relationship between Category and ProductTopSellerCategory with just the many side mapped. Normally you would use the inverse attribute on the collection mapped on the one side but you don't have that mapped so I suggest:

using (var txn = session.BeginTransaction())
{
   var category = new Category() { Name = "Test 1" };
   session.Save(category);
   session.Flush();

   var product = session.Get<Product>(1);
   var productTopSellerCategory = new ProductTopSellerCategory() { Product = product, Category = category };
   session.Save(productTopSellerCategory);
   txn.Commit();
}

The problem with your original code is that NHibernate is attempting to insert the new ProductTopSellerCategory then update the category. It's doing this because the inverse attribute is not set. Forcing NHibernate to insert the new Category by flushing the session should resolve the problem.

answered Jul 20 '13 at 13:30

Jamie Ide

48,427
16
81
117

Thanks, I tried doing a flush after insert the category but I get the same problem. – nfplee Jul 20 '13 at 14:46
I just noticed the ReadOnly settings on ProductTopSellerCategory .Category and .Product. ReadOnly will not allow the value to be set and your original code may work when you remove it. If this doesn't fix the problem, profile your application so that you can see exactly what's happening. – Jamie Ide Jul 20 '13 at 17:40
Thanks I will try Monday as I'm tied up until then. FYI the structure I'm using came from the NHibernate Blog. http://nhforge.org/blogs/nhibernate/archive/2010/06/30/nhibernate-and-composite-keys.aspx – nfplee Jul 20 '13 at 18:51
If I take the ReadOnly off I get the error: "Invalid index 3 for this SqlParameterCollection with Count=3. ". I have also tried adding a collection of TopSellers to the category (with a has many mapping and an inverse) but I get the same problem. Looking at the SQL when it tries to insert the top seller it has a category id of 0. Let me know if you need anymore information to help once more. Thanks – nfplee Jul 22 '13 at 09:14

score 0 · Accepted Answer · answered Jul 22 '13 at 09:50

I think I've found a solution. It's a little bit of a hack but it meant I didn't have to change my entities and mappings. The issue happens because the CategoryId in the identity type doesn't point to the same reference as the Category.Id in the top sellers entity. To fix this issue I need to add the following just before I insert the top seller:

topSeller.Id.CategoryId = topSeller.Category.Id;

Batch Insert - Foreign Key Not Working

2 Answers2