LINQ to SQL. Cascade delete sumulation
I have many-to-many relation between persons and addresses
Person { Id, ... }
Address { Id, ... }
Address2Person { Id, PersonId, AddressId }
DBML-generated classes looks like (there are PK and FK on diagram):
class Person {
Id, Address2Person
}
class Address2Person {
Id, Person, PersonId, Address, AddressId
}
class Address {
Id, Address2Person
}
When I delete person I have to delete AddressPerson rows (and it's easy) as well as appropriate Address rows but only when there are no external references to address except the one is being deleted.
May be it would be smarter to implement cascade delete on DB level, but how I can emulate this in pure LINQ 2 SQL ?
Finally I coded something like that:
开发者_StackOverflow社区// finding out addresses to delete
var addressesToDelete =
(from ap in DataBase.Address2Person
group ap by ap.Address
into g
where g.All(x => x.PersonId == personId2Delete)
select g.Key).ToList();
// killing references
DataBase.Address2Person.DeleteAllOnSubmit(
DataBase.Address2Person.Where(x => x.PersonId == personId2Delete));
// killing addresses
DataBase.Address.DeleteAllOnSubmit(addressesToDelete);
Is there a better way?
Thank you in advance!
If your rule is that you cannot have orphaned addresses, you might get better performance and easier code maintenance from the following sequence:
DataBase.Address2Person.DeleteAllOnSubmit(
DataBase.Address2Person.Where(x => x.PersonId == personId2Delete));
DataBase.Address.DeleteAllOnSubmit(
DataBase.Address.Where(x => !x.Address2Person.Any());
精彩评论