LINQ to SQL Insert Failing because of Join

Answer 1

NAILED IT!!!

So basically in my Service layer I had...

    Public Sub AddException(ByVal ex As Exception, Optional ByVal notes As String = Nothing) Implements IHealthMonitorService.AddException
        Dim exception As HealthMonitor = New HealthMonitor
        Dim userID As Integer = Nothing
        If HttpContext.Current.User.Identity.IsAuthenticated Then userID = Authentication.CustomAuthentication.RetrieveAuthUser.ID

        Dim DataConverter As Utilities.DataConverters = New Utilities.DataConverters
        Dim InformationHelper As Utilities.InformationHelper = New Utilities.InformationHelper

        With exception
            .DateTime = DateTime.Now
            .Exception = ex.ToString
            .Message = ex.Message
            .Notes = notes
            .ShortMessage = If(ex.Message.Length > 50, ex.Message.Substring(0, 50), ex.Message)
            .Source = ex.Source
            .StackTrace = ex.StackTrace
            .Url = HttpContext.Current.Request.Url.ToString()
            .UserID = userID
            .UserIP = DataConverter.IPAddressToNumber(InformationHelper.GetUserIP)
            .UserOS = InformationHelper.GetUserOS()
            .UserBrowser = InformationHelper.GetUserBrowser()
        End With

        _HealthMonitorRepository.AddException(exception)
    End Sub

The problem with this is that when I send userID to the exception object, It was sending 0 (ZERO) to the database. here's the solution

        Dim userID As Integer? = Nothing ''# (?) makes the integer nullable.

Answer 2

To ensure you have a User and therefor don't violate the foreign key, could you do something like:

`Public Sub AddException(ByVal exception As HealthMonitor) Implements IHealthMonitorRepository.AddException
    ' take what you need from exception to Create a User
    User user = new User{Populate properties here}
    dc.Users.InsertOnSubmit(user)   
    dc.HealthMonitors.InsertOnSubmit(exception)
    dc.SubmitChanges()  ''# ERROR HERE
End Sub'

and that way you create the user if you need it.

Or you could do an existence check for the user.