CODEWITHSUNDEEP
c#asp.netsql-serverlinq
Peter
Peter

Reputation: 47

asp.NET LINQ Delete from database

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?

Upvotes: 3

Views: 230

Answers (3)

Nayas Subramanian
Nayas Subramanian

Reputation: 2379

Cascade on Delete is there in entity framework. Cascade delete automatically deletes dependent records or set null to foreignkey properties when the principal record is deleted.

This is one to many reletionship between parent and child

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{   
    modelBuilder.Entity<User>()
        .HasOptional(a => a.UserDetail)
        .WithOptionalDependent()
        .WillCascadeOnDelete(true);
}

For more details check this: http://www.entityframeworktutorial.net/code-first/cascade-delete-in-code-first.aspx

Upvotes: 0

Alisom Martins
Alisom Martins

Reputation: 738

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!

Upvotes: 0

marc_s
marc_s

Reputation: 755491

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

Upvotes: 1

Related Questions