Many to Many with LINQ-To-Sql and ASP.NET MVC

Question

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

Answer 1

I have recently struggled greatly with many to many relationships in MVC. I've finally gotten mine working. I'd love to help you out, but do not fully understand your problem.

When you say that the query returns 2 rows, I would think that it should since there are 2 communications for that problem. If you want to return problems for a student, you do not need to query the ProbComms table, just the Problem table. Then if you want to return communications for those problems, query the ProbComms table.

For your loop, you need to loop through the problems and then within that loop, loop through the communications specific to that problem. Something very loosely like:

foreach (var p in Model.Student.Problem)
{
     <%= Html.Encode(p.ProblemInfoField) %>
     ...
     foreach (var c in p.ProbComms)
     {
        <%= Html.Encode(c.Communications.CommunicationInfoField) %>
         ...
     }
}

You do not need to select the ProbComms in the controller and send them to the view. They should already be linked. Only the student needs to be sent to the view.