当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Deleting objects through foreign key relationship with T-SQL query

问题描述:

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.

网友答案:

You should always show table structures and foreign key linkages. However, it should look something like this.

This assumes the link between the two tables is productshell.shellid=shellmembers.shellid

delete productshell
where shellid in (
    select shellid
    from shellmembers
    where productid={0}
    )

It can also be written as a join

delete productshell
from productshell
join shellmembers on productshell.shellid=shellmembers.shellid
where shellmembers.productid={0}
分享给朋友:
您可能感兴趣的文章:
随机阅读: