Adding object to another using Entity Framework, reciving an error

Question

I'm trying to add a product to a category using this code

 public void AddProductToCategory()
        {
            if (categoryExist)
            {  
                var product = FindProduct(productArticleNumber);

                var productExist = product != null;

                if (productExist)
                {
                    AddProduct(product);

                }


        }

im getiing an error saying:

Cannot insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.

Here is my tables:

CREATE TABLE Product (
Id INT IDENTITY PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
ArticleNumber NVARCHAR(50) NOT NULL,
DESCRIPTION NVARCHAR (500),
Price INT,
UNIQUE (Articlenumber),
UNIQUE (Name)
)

CREATE TABLE Categories(
Id INT IDENTITY PRIMARY KEY,
Name NVARCHAR (10),
ProductId INT,
FOREIGN KEY (ProductId)
 REFERENCES Product (Id)
)

Here are the

AddProduct

Code:

private static void AddProduct(Product product)
        {
            context.Product.Add(product);

            context.SaveChanges();
        }

I tried to set identity_Insert to On like that:

SET IDENTITY_INSERT Product ON

But im still getting the same error

*I'm trying to add a product to a category using this code* No, you are not ... you are not addding product to Categories but to Products ... — Selvin, Nov 16 '22 at 10:57
Please refer this https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity — Purendra Naik, Nov 16 '22 at 10:59
off topic: you should consider to normalize Categories, too (Products <-> ProductCategory <-> Categories) ... with current solution you 1. cannot has multiple category for product 2. in fact different products can't have the same category (well, they have the same name but in fact it would be different "Category")... — Selvin, Nov 16 '22 at 10:59
@Selvin From what i got from you, i should create a ProductCategory table? and have two foreign keys which are ProductId and CategoryId? — Malek Mshinesh, Nov 16 '22 at 11:45

score 0 · Accepted Answer · answered Nov 16 '22 at 12:26

It's a bit unclear what are you trying to do... insert new product or change existing...

In your code you're checking if product exists and, if it does, you're adding it again. So, you're duplicating product (product with same primary key) which database doesn't allow. You should do something like this:

public void AddProductToCategory()
{
    if (categoryExist)
    {  
        var product = FindProduct(productArticleNumber);

        var productExist = product != null;

        if (!productExist)
        {
            AddProduct();
        }

    }
}

private static void AddProduct()
{
    Product newProduct = new Product()
    {
        Name = "some name",
        ArticleNumber = "123"
        //etc...
    }
    
    context.Product.Add(newProduct);

    context.SaveChanges();
}

or, if you want to enter new product with same data like existing one, copy everything from existing to new product and add that one. Remember to omit ID, so that database assigns new ID (identity)

In that case you can to something like this:

public void AddProductToCategory()
{
    if (categoryExist)
    {  
        var product = FindProduct(productArticleNumber);

        var productExist = product != null;

        if (productExist)
        {
            AddProduct(product);
        }

    }
}

private static void AddProduct(Product existingProduct)
{
    Product newProduct = new Product()
    {
        Name = existingProduct.Name
        ArticleNumber = existingProduct.ArticleNumberr
        //etc...
        //DON'T DO THIS:
        //Id = existingProduct.Id
    }

    context.Product.Add(newProduct);

    context.SaveChanges();
}

Adding object to another using Entity Framework, reciving an error

1 Answers1