问题描述:

I have a SQL Server database with 2 tables:

t1 - Category

Id

Name

t2- Product

Id

Name

CategoryId

I want to delete a row from the Category table, but since I have the foreign key I need to handle the products that has the CategoryId I want to delete.

So I did this:

var ProdCatID = (from prod in DataContext.Products

where prod.CategoryId == Convert.ToInt32(Id)

select prod).First();

ProdCatID.CategoryId = null;

DataContext.SubmitChanges();

var DelCat = (from cat in DataContext.Categories

where cat.Id == Convert.ToInt32(Id)

select cat).ToList();

DataContext.Categories.DeleteAllOnSubmit(DelCat);

DataContext.SubmitChanges();

What Im trying to do is to check if there is any product with thatCategoryId, if there is - I want to set theCategoryIDto null and then delete the row from theCategory` table.

It is working when I have a product with a CategoryId but when I can't delete it.

Any ideas?

网友答案:

You're only setting the first product that has this CategoryID to null - you need to handle all products that have that ID !

var products = (from prod in DataContext.Products
                where prod.CategoryId == Convert.ToInt32(Id)
                select prod).ToList();

foreach(Product p in products)
{
    p.CategoryId = null;
}

DataContext.SubmitChanges();

.....

After that, now you should be able to delete the category from the table

网友答案:

Simple! Change the Product table configuration in Database!

ALTER TABLE Product 
ADD CONSTRAINT 'Category_FK'
    FOREIGN KEY (CategoryId)
    REFERENCES Category(Id)
    ON DELETE SET NULL;

whenever you delete a primary key will automatically put null!

相关阅读:
Top