Asp.Net MVC 4 - Adding an object model to an ICollection within another object model

Question

Some background

I have 2 tables in my Asp.Net MVC 4 Database first application, Submission and Comment. (There are a great deal more but they are irrelevant for this question). Any given Submission can have any number of Comments (One to many) associated to it.

I have a submission details page, on which a user can open a jQuery dialog box with a partial view inside it to add a comment to the submission. This partial view is bound to a strongly typed viewmodel created to add comments.

Tables/Models

Submission Table

Here is the class model for the Submission table. (I have omitted some properties for simplicity).

Partial Public Class Submission
    Public Property Submission_ID As Integer
    Public Property Submission_Hash As String
    Public Property Created As Nullable(Of Date)
    Public Property Modified As Date

    Public Overridable Property Comments As ICollection(Of Comment) = New HashSet(Of Comment)
End Class

Comment Table

And here is the class model for the Comment table. (Again, I have omitted some properties for simplicity).

Partial Public Class Comment
    Public Property Comment_ID As Integer
    Public Property User_ID As Nullable(Of Integer)
    Public Property Comment_Type As Nullable(Of Integer)
    Public Property Comment1 As String
    Public Property Created As Nullable(Of Date)
    Public Property Modified As Date

    Public Overridable Property Comment_Type1 As Comment_Type
    Public Overridable Property User As User
    Public Overridable Property Submissions As ICollection(Of Submission) = New HashSet(Of Submission)
End Class

The Requirement

What I am trying to do, is add a new comment to the collection of comments that already exist for a given submission.

So far...

So far, I have a view model for the add comment dialog box which gets initialised in my controller and then returns the populated view model back to controllers post method. Here is the code

ViewModel

Public Class SubmissionAddCommentViewModel

    <Display(Name:="Submission")> _
    Public Property Submission_ID As Integer

    <Display(Name:="User")> _
    Public Property User_ID As Integer

    <Display(Name:="Comment type")> _
    Public Property Comment_Type As Integer

    <Required(ErrorMessage:="Please enter a comment.")> _
    <Display(Name:="Comment")> _
    Public Property Comment As String

End Class

ViewModel Builder

Public Class SubmissionAddCommentViewModel_Builder

    Implements IModelBuilder(Of SubmissionAddCommentViewModel, Comment)
    ReadOnly db As GeosightEntities
    ReadOnly submission As Submission

    Public Sub New(db As GeosightEntities, submission As Submission)
        Me.db = db
        Me.submission = submission
    End Sub

    Public Function CreateFrom(entity As Comment) As SubmissionAddCommentViewModel Implements IModelBuilder(Of SubmissionAddCommentViewModel, Comment).CreateFrom
        Dim model = New SubmissionAddCommentViewModel()

        model.Submission_ID = submission.Submission_ID
        model.User_ID = GetLoggedIn_ID()

        Return model
    End Function

    Public Function Rebuild(model As SubmissionAddCommentViewModel) As SubmissionAddCommentViewModel Implements IModelBuilder(Of SubmissionAddCommentViewModel, Comment).Rebuild
        Return model
    End Function

    Public Sub Add(model As SubmissionAddCommentViewModel)
        Dim comment As New Comment

        ' Map the ViewModel and Model fields
        comment.Comment_Type1 = If(IsNothing(model.Comment_Type), Nothing, db.Comment_Type.Find(model.Comment_Type))
        comment.User = db.Users.Find(model.User_ID)
        comment.Comment1 = model.Comment
        comment.Modified = Now

        ' Add the comment
        db.Submissions.Find(model.Submission_ID).Comments.Add(comment)
        db.SaveChanges()
    End Sub

End Class

Controller Get Method

Function AddComment(Optional ByVal id As Integer = Nothing) As ActionResult

    Dim commentAddView As New SubmissionAddCommentViewModel
    Dim submission As Submission = db.Submissions.Find(id)
    Dim comment As New Comment

    If IsNothing(submission) Then
        Return HttpNotFound()
    End If

    Me.builder_AddComment = New SubmissionAddCommentViewModel_Builder(db, submission)

    ' Create the instance of the submission add comment view model
    commentAddView = builder_AddComment.CreateFrom(comment)

    If Request.IsAjaxRequest Then
        Return PartialView("AddCommentPartial", commentAddView)
    End If

    Return View(commentAddView)
End Function

Controller Post method

<HttpPost()> _
Function AddComment(ByVal commentAddView As SubmissionAddCommentViewModel) As ActionResult

    Dim comment As New Comment
    builder_AddComment = New SubmissionAddCommentViewModel_Builder(db, db.Submissions.Find(commentAddView.Submission_ID))

    ' Handle invalid comment models
    If Not ModelState.IsValid Then

        If Request.IsAjaxRequest Then
            Return PartialView("AddCommentPartial", commentAddView)
        End If

        Return View(commentAddView)
    End If

    ' Add the comment
    Me.builder_AddComment.Add(commentAddView)

    ' Display the successful message
    If Request.IsAjaxRequest Then
        Return PartialView("AddCommentSuccessPartial", commentAddView)
    End If

    Return RedirectToAction("Details", New With {.id = commentAddView.Submission_ID})

End Function

Question

When testing the above functionality, execution is successful all the way through to db.SaveChanges() in the ViewModel builder, however execution just stops once this line has been run. I have no idea what is happening and as a result am completely stuck.

Does anyone know whether A) I am going about adding the comment the right way? B) Why this line is stopping execution without any kind of error?

---

Update

Thank you to Peter Smith for his suggestion of putting a Try Catch around the code, I dont know why I didn't think of or do that in the first place.

I am now getting the follwing error:

{MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT" & vbLf & " Submission_Comment.Submission_ID, " & vbLf & " Submission_Comment.' at line 1" & vbCrLf & " at MySql.Data.MySqlClient.MySqlStream.ReadPacket()" & vbCrLf & " at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)" & vbCrLf & " at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)" & vbCrLf & " at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)" & vbCrLf & " at MySql.Data.MySqlClient.MySqlDataReader.NextResult()" & vbCrLf & " at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)" & vbCrLf & " at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()" & vbCrLf & " at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()" & vbCrLf & " at MySql.Data.Entity.EFMySqlCommand.ExecuteNonQuery()" & vbCrLf & " at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues)" & vbCrLf & " at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)}

Answer 1

I managed to find the problem and implement a solution. In order to help others who might come across the same issue as me, here was my solution.

In my MySQL schema, the intermediate table (Submission_Comment) between the Submission and Comment tables has 2 columns...Submission_ID and Comment_ID.

When I set up the tables, I made these columns Foreign keys but forgot to make them a composite primary key. Making the 2 columns a composite primary key fixed my issue.