How do I display Info from a two tables in view where the user chooses the ID of the selected item?

See more:

VB

ASP.NET

MVC

I am creating mvc application in vb.net and I am trying to use a ViewModel to display information about two Tables from SQL.

So far I can successfully display the information for one Table but I am unsure how to display the information from the other table.

The IssueTable is being displayed when the user selects its ID, but the table that I want to display with it is the CommentsTable.

There is a one-to-many relationship between the IssueTable and the CommentsTable, so for example I want to display the IssueSummary and for each IssueID that matches the IssueID in the commentsTable I want to display the commentBody which will mean each issueSummary would have multiple comments.

So far this is what I have got.

I created a class like this:

Namespace IssueTracker
    Public Class IssueComments
        Public cTable As CommentTable 'CommentTable in SQL database
        Public iTable As IssueTable 'IssueTable in SQL database
    End Class
End Namespace 

Then in the controller I got this:

Dim IssTable As IssueTable = dbServer.IssueTables.Find(id) 'getting the ID of the selected issue

Dim iss As New IssueTable() With {
    .IssueID = IssTable.IssueID,
    .IssueKey = IssTable.IssueKey,
    .IssueTypeID = IssTable.IssueTypeID,
    .priorityID = IssTable.priorityID,
    .IssueSummary = IssTable.IssueSummary,
    .IssueDescription = IssTable.IssueDescription,
    .Created = IssTable.Created,
    .Updated = IssTable.Updated
} ' This will display the information about the specific issue crrectly

Here I am trying to display the information about the comments:

Dim comment As New CommentTable() With {
    .CommentID = cTable.CommentID,
    .CommentBody = cTable.CommentBody
} 'Will not display anything as it is not correctly connected with issueTable

Dim viewModelC As New IssueTracker.IssueComments() With {
    .cTable = comment,
    .iTable = iss
} 'Here I create the variable viewModel the return it in the view 

Return View(viewModelC)

I am trying to connect the two tables like this:

Dim viewModel = From c In dbServer.CommentTables Join i In dbServer.IssueTables On c.IssueID Equals i.IssueID Where (c.IssueID = i.IssueID)
Select New IssueTracker.IssueComments With {.cTable = c, .iTable = i}

How can I properly display all comments for each issueID that is in the commentsTable that is equal to the issueID in the IssueTable?