Adding object to another using Entity Framework, reciving an error-CodePudding

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

CodePudding user response：

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();
}