Deleting objects through foreign key relationship with T-SQL query

Posted by LaserBeak on Stack Overflow See other posts from Stack Overflow or by LaserBeak
Published on 2012-09-25T03:26:26Z Indexed on 2012/09/25 3:37 UTC
Read the original article Hit count: 206

Filed under:

Looking for a way to write the following LinQ to entities query as a T-SQL statement.

repository.ProductShells.Where(x => x.ShellMembers.Any(sm => sm.ProductID == pid)).ToList().ForEach(x => repository.ProductShells.Remove(x));

The below is obviously not correct but I need it to delete respective ProductShell object where any ShellMember contains a ProductID equal to the passed in variable pid. I would presume this would involve a join statement to get the relevant ShellMembers.

repository.Database.ExecuteSqlCommand("FROM Shellmembers WHERE ProductID={0} DELETE FK_ProductShell", pid);

I have cascade delete enabled for the FK_ShellMembers_ProductShells foreign key, so when I delete the ProductShell it will delete all the ShellMembers that are associated with it. I am going to pass this statement to System.Data.Entity Database.ExecuteSqlCommand method.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server