Linq Remove
I have a question table that joins the decision table. The solutions are related to questions, but in order to delete a question, I must first delete the solutions for that question and then delete the question itself.
I have a linq query that fetches all the solutions for a specific question, but I'm not sure how to proceed with uninstalling the solutions and then hence proceeding to delete the question.
Here is the code, it gets 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;
}
}
a source to share
If you have a foreign key relationship between your questions and solutions, just set it so that duplications are removed (CASCADE ON DELETE). So you have to delete this question and the solutions are automatically deleted in the database.
Using a foreign key relationship will also give you an entity set on a Question object that will directly load the related solution objects and make it so you can write the join logic all the time. You will need to remove and re-add your entities after adding the foreign key relationship for the designer to bring it up - or you can add the association manually in the designer.
a source to share
I think using Join is the root cause of the problem. Try to create a mapping in DBML between Questions and Soutions, then you can simply write:
var questions = from q in Questions where q.QuestionText.Contains(qTextInput);
var solutions = questions.SelectMany(q => q.Solutions);
...
dc.Solutions.DeleteAllOnSubmit(solutions);
a source to share
The problem is that it matchedSol
doesn't have a type IEnumerable<Solution>
. I would be surprised if your code even compiles.
What you need is this (example uses LINQ2Objects, but it doesn't matter):
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 is a LINQ expression enumerated four solutions: P
, Q
, S
and T
. This enum can be assigned to your method DeleteOnSubmit
.
a source to share