Linq to sql - delete and if fails continue

If I have a table like:

StudentId  | ... | SchoolId
___________|_____|__________ 
1          | ... | SchoolA
2          | ... | SchoolA
3          | ... | SchoolB
...

And I want to delete a list of schools, from schoolA to schoolZ (using LINQ-to-SQL):

foreach(School s in schools){
    db.Schools.DeleteOnSubmit(s);
    db.submitChanges();
}

SchoolA and SchoolB will fail because of the FK references above

How can I continue and delete all other scho开发者_开发知识库ols, discarding the ones where the exception occurred?

Only include schools that don't have any students:

var schoolsToDelete = schools.Where(x => !x.Students.Any());
db.Schools.DeleteAllOnSubmit(schoolsToDelete); 
db.submitChanges();

By default, LINQ to SQL fails on the first error and rolls back the transaction. If you want it to keep working on anything it can, you can pass in the ConflictMode overload on SubmitChanges to allow it to keep going. The following sample from "LINQ in Action" tries to issue all of the queued updates and then output the conflicts that were encountered by handling the ChangeConflictException.

try
{
    context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
    var exceptionDetail = 
        from conflict in context.ChangeConflicts
        from member in conflict.MemberConflicts
        select new
        {
            TableName = GetTableName(context, conflict.Object),
            MemberName = member.Member.Name,
            CurrentValue = member.CurrentValue.ToString(),
            DatabaseValue = member.DatabaseValue.ToString(),
            OriginalValue = member.OriginalValue.ToString()
        };
    exceptionDetail.Dump();
}

Naturally, it is much better to be proactive and only try to delete the records that are valid as Mark Cidade demonstrated.

I agree with Mark Cidade, but I can suggest an improvement of using a join scope to send a single request to the database server.

I agree with Mark's solution. If you wan't to delete the school and its students, you can use:

foreach (School s in schools)
{
    db.Students.DeleteAllOnSubmit(s.Students);
    db.Schools.DeleteOnSubmit(s);
}

db.submitChanges();

That way you are fulfilling the FK constraint, so no errors are thrown.

Found out a simple way

foreach(School s in schools){

    try{
        db.Schools.DeleteOnSubmit(s);

        db.submitChanges();
    }
    catch(SqlException exp){
        if(exp.Message.Contains("The DELETE statement conflicted with the REFERENCE constraint"))   //just checking if is FK reference
            db.Schools.InsertOnSubmit(s);   //=)
        else
            throw;

    }
}