Many to Many with LINQ-To-Sql and ASP.NET MVC
- by Jonathan Stowell
Hi All,
I will restrict this to the three tables I am trying to work with Problem, Communications, and ProbComms. The scenario is that a Student may have many Problems concurrently which may affect their studies. Lecturers may have future communications with a student after an initial problem is logged, however as a Student may have multiple Problems the Lecturer may decide that the discussion they had is related to more than one Problem.
Here is a screenshot of the LINQ-To-Sql representation of my DB:
LINQ-To-Sql Screenshot
At the moment in my StudentController I have a StudentFormViewModel Class:
//
//ViewModel Class
public class StudentFormViewModel
{
IProbCommRepository probCommRepository;
// Properties
public Student Student { get; private set; }
public IEnumerable<ProbComm> ProbComm { get; private set; }
//
// Dependency Injection enabled constructors
public StudentFormViewModel(Student student, IEnumerable<ProbComm> probComm)
: this(new ProbCommRepository())
{
this.Student = student;
this.ProbComm = probComm;
}
public StudentFormViewModel(IProbCommRepository pRepository)
{
probCommRepository = pRepository;
}
}
When I go to the Students Detail Page this runs:
public ActionResult Details(string id)
{
StudentFormViewModel viewdata = new StudentFormViewModel(studentRepository.GetStudent(id),
probCommRepository.FindAllProblemComms(id));
if (viewdata == null)
return View("NotFound");
else
return View(viewdata);
}
The GetStudent works fine and returns an instance of the student to output on the page, below the student I output all problems logged against them, but underneath these problems I want to show the communications related to the Problem.
The LINQ I am using for ProbComms is This is located in the Model class ProbCommRepository, and accessed via a IProbCommRepository interface:
public IQueryable<ProbComm> FindAllProblemComms(string studentEmail)
{
return (from p in db.ProbComms
where p.Problem.StudentEmail.Equals(studentEmail)
orderby p.Problem.ProblemDateTime
select p);
}
However for example if I have this data in the ProbComms table:
ProblemID CommunicationID
1 1
1 2
The query returns two rows so I assume I somehow have to groupby Problem or ProblemID but I am not too sure how to do this with the way I have built things as the return type has to be ProbComm for the query as thats what Model class its located in.
When it comes to the view the Details.aspx calls two partial views each passing the relevant view data through, StudentDetails works fine page:
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<MitigatingCircumstances.Controllers.StudentFormViewModel>" %>
<% Html.RenderPartial("StudentDetails", this.ViewData.Model.Student); %>
<% Html.RenderPartial("StudentProblems", this.ViewData.Model.ProbComm); %>
StudentProblems uses a foreach loop to loop through records in the Model and I am trying another foreach loop to output the communication details:
<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<IEnumerable<MitigatingCircumstances.Models.ProbComm>>" %>
<script type="text/javascript" language="javascript">
$(document).ready(function() {
$("DIV.ContainerPanel > DIV.collapsePanelHeader > DIV.ArrowExpand").toggle(
function() {
$(this).parent().next("div.Content").show("slow");
$(this).attr("class", "ArrowClose");
},
function() {
$(this).parent().next("div.Content").hide("slow");
$(this).attr("class", "ArrowExpand");
});
});
</script>
<div class="studentProblems">
<% var i = 0;
foreach (var item in Model) { %>
<div id="ContainerPanel<%= i = i + 1 %>" class="ContainerPanel">
<div id="header<%= i = i + 1 %>" class="collapsePanelHeader">
<div id="dvHeaderText<%= i = i + 1 %>" class="HeaderContent"><%= Html.Encode(String.Format("{0:dd/MM/yyyy}", item.Problem.ProblemDateTime))%></div>
<div id="dvArrow<%= i = i + 1 %>" class="ArrowExpand"></div>
</div>
<div id="dvContent<%= i = i + 1 %>" class="Content" style="display: none">
<p>
Type: <%= Html.Encode(item.Problem.CommunicationType.TypeName) %>
</p>
<p>
Problem Outline: <%= Html.Encode(item.Problem.ProblemOutline)%>
</p>
<p>
Mitigating Circumstance Form: <%= Html.Encode(item.Problem.MCF)%>
</p>
<p>
Mitigating Circumstance Level: <%= Html.Encode(item.Problem.MitigatingCircumstanceLevel.MCLevel)%>
</p>
<p>
Absent From: <%= Html.Encode(String.Format("{0:g}", item.Problem.AbsentFrom))%>
</p>
<p>
Absent Until: <%= Html.Encode(String.Format("{0:g}", item.Problem.AbsentUntil))%>
</p>
<p>
Requested Follow Up: <%= Html.Encode(String.Format("{0:g}", item.Problem.RequestedFollowUp))%>
</p>
<p>Problem Communications</p>
<% foreach (var comm in Model) { %>
<p>
<% if (item.Problem.ProblemID == comm.ProblemID)
{ %>
<%= Html.Encode(comm.ProblemCommunication.CommunicationOutline)%>
<% } %>
</p>
<% } %>
</div>
</div>
<br />
<% } %>
</div>
The issue is that using the example data before the Model has two records for the same problem as there are two communications for that problem, therefore duplicating the output.
Any help with this would be gratefully appreciated.
Thanks,
Jon
