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?

Related posts

Recent Viewed