asp.NET LINQ Delete from database

Question

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?

where cat.Id == Convert.ToInt32(Id) will never handle the case where a product has a null category ID. — JimmyV
– JimmyV, Commented Nov 13, 2015 at 17:36
It is working when a CategoryId is used by a product. But when the CategroId is not used by any product I can`t delete it. why? — Peter
– Peter, Commented Nov 13, 2015 at 17:43
do you have child records perhaps.. you have to make sure that if there are 1 to many relationships etc.. that you delete from bottom up meaning the parent record will be the last to be deleted.. this will prevent having orphan records in your Database.. also why don't you wrap the code around a try catch where you are trying to delete and tell us what the error message is.. that will help you to understand why things are not being deleted.. — MethodMan
– MethodMan, Commented Nov 13, 2015 at 17:49
This is what I got in try - catch : Object reference not set to an instance of an object — Peter
– Peter, Commented Nov 13, 2015 at 17:59

marc_s · Accepted Answer · 2015-11-13 18:12:20Z

1

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

answered Nov 13, 2015 at 18:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

THANK YOU VERY MUCH !!! I can`t vote for your answer but you helped me! I got my mistake ... again - thank you very much!

marc_s Over a year ago

@Peter: but you can accept this answer !! :-) ;-) :-) This will show your appreciation for the people who spent their own time to help you.

Alisom Martins · Accepted Answer · 2015-11-13 18:25:34Z

0

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!

answered Nov 13, 2015 at 18:25

Alisom Martins

7389 silver badges17 bronze badges

Comments

Nayas Subramanian · Accepted Answer · 2017-05-26 11:41:41Z

0

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

answered May 26, 2017 at 11:41

Nayas Subramanian

2,38723 silver badges32 bronze badges

Collectives™ on Stack Overflow

asp.NET LINQ Delete from database

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related