C# Linq Delete over Join

Question

Hello, I have a Question table which joins to a Solution table. The solutions are linked to the questions but in order to delete a question, i must delete the solutions for that question first and then delete the question itself.

I have a linq query that retrieves all solutions for a specified question however i am unsure how to proceed with the deletion of the solutions and then consequently proceed to delete the question. Help appreciated greatly.

Here is the code, it recieves overload error messages:

public static void DeleteSol(string qTextInput)
{
        ExamineDataContext dc = new ExamineDataContext();
        var matchedSol = from q in dc.Questions
                              where q.QuestionText.Contains(qTextInput)
                              join s in dc.Solutions
                              on q.QuestionID equals s.QuestionID
                              into qs // note grouping        
                              select new
                              {
                                  solution = qs
                              };
        try
        {
            dc.Solutions.DeleteOnSubmit(matchedSol);
            dc.SubmitChanges();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Answer 1

I think that using Join is the root cause of the problem. Try to create a mapping in the DBML between Questions and Soutions, then you will be able to simply write:

var questions = from q in Questions where q.QuestionText.Contains(qTextInput);
var solutions = questions.SelectMany(q => q.Solutions);
...
dc.Solutions.DeleteAllOnSubmit(solutions);

Answer 2

If you have a foreign key relationship between your questions and solutions, just set it so that deletes are propagated (CASCADE ON DELETE). That way you only have to delete the question and the solutions are automatically deleted by the database. Using the foreign key relationship will also give you an entity set on your Question entity that will directly load the related Solution entities and make it so you can avoid writing the join logic all the time. You'll need to delete and re-add your entities after adding the foreign key relationship for the designer to pick it up -- or you can add the association by hand in the designer.

Answer 3

The problem is that matchedSol is not of type IEnumerable<Solution>. I would be surprised if your code even compiles.

What you need is this (the example uses LINQ2Objects but that doesn't make a difference):

static void Main()
{
    var questions = new[]
                        {
                            new { QuestionID = 1, QuestionText = "ABCDEF" },
                            new { QuestionID = 2, QuestionText = "GHIJKL" },
                            new { QuestionID = 3, QuestionText = "ABCXYZ" },
                        };
    var solutions = new[]
                        {
                            new { QuestionID = 1, Solution = "P" },
                            new { QuestionID = 1, Solution = "Q" },
                            new { QuestionID = 2, Solution = "R" },
                            new { QuestionID = 3, Solution = "S" },
                            new { QuestionID = 3, Solution = "T" },
                            new { QuestionID = 4, Solution = "U" },
                        };
    var qTextInput = "ABC";
    var matchedSol = from q in questions
                     where q.QuestionText.Contains(qTextInput)
                     join s in solutions
                     on q.QuestionID equals s.QuestionID
                     select s;

    foreach (var solution in matchedSol)
    {
        Console.WriteLine("Solution " + solution.Solution +
                          " for question " + solution.QuestionID);
    }
}

The result of the LINQ expression is an enumerable with four solutions: P, Q, S and T. This enumerable can be given to your DeleteOnSubmit method.