LINQ to SQL Insert Failing because of Join

Question

I've got two tables in my database Users and HealthMonitor. In the HealthMonitor table I have a UserID field that is mapped to the ID field in the Users table.

Pretty straight forward so far...

I left the UserID field in the HealthMonitor table as "Nullable" so that I can have the system insert a NULL value into the table if there is no current User logged in... IE: if an error is thrown to a non authenticated user.

The problem is that the insert fails when I'm trying to insert NULL into that UserID field.

FYI: removing the relationship also removes the problem, however I know it's probably not the best way to accomplish the task

EDIT:

After re applying the relationship and ensuring that UserID was nullable, it appears to work now... however the value is inserted as 0 not null

    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

Am I missing something here? can it be inserted as NULL

EDIT:

OK, So I lied a little. I'm still getting the error

    Public Sub AddException(ByVal exception As HealthMonitor) Implements IHealthMonitorRepository.AddException
        dc.HealthMonitors.InsertOnSubmit(exception)
        dc.SubmitChanges()  ''# ERROR HERE
    End Sub

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_un_HealthMonitor_un_Users". The conflict occurred in database "UrbanNow", table "dbo.un_Users", column 'ID'. The statement has been terminated.

Here's the Table

/****** Object:  Table [dbo].[un_HealthMonitor]    Script Date: 07/24/2010 10:48:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[un_HealthMonitor](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NULL,
    [Exception] [nvarchar](4000) NULL,
    [Url] [nvarchar](2048) NULL,
    [Source] [nvarchar](4000) NULL,
    [ShortMessage] [nvarchar](50) NULL,
    [Message] [nvarchar](4000) NULL,
    [StackTrace] [nvarchar](4000) NULL,
    [UserIP] [nvarchar](50) NULL,
    [UserBrowser] [nvarchar](50) NULL,
    [UserOS] [nvarchar](50) NULL,
    [UserIsMoblie] [bit] NULL,
    [DateTime] [datetime2](0) NULL,
    [Notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK_un_HealthMonitor] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[un_HealthMonitor]  WITH CHECK ADD  CONSTRAINT [FK_un_HealthMonitor_un_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[un_Users] ([ID])
GO

ALTER TABLE [dbo].[un_HealthMonitor] CHECK CONSTRAINT [FK_un_HealthMonitor_un_Users]
GO

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.