LINQ to SQL Violation of UNIQUE KEY constraint with NULL column

Question

I have the following table. When i try to insert records using LINQ to SQL it works fine until I try to insert a record where the UserId is null. When I try that I get the following error

"UNIQUE KEY constraint Cannot insert duplicate key in object"

I am only trying to insert 1 record and the table is empty so there is definitely no clash on the unique constraint.

CREATE TABLE [dbo].[QuickViewUserModule](
    [QuickViewUserModuleId] [int] IDENTITY(1,1) NOT NULL,
    [QuickViewModuleId] [int] NOT NULL,
    [UserId] [int] NULL,
    [SortOrder] [tinyint] NOT NULL,
... More key stuff ...
    CONSTRAINT [IX_QuickViewUserModule] UNIQUE NONCLUSTERED  (  
        [UserId] ASC,   
        [QuickViewModuleId] ASC)
)

Answer 1

It seems to have more to do with the constraint itself than with LINQ to SQL. I'm no expert, but maybe the UNIQUE CONSTRAINT won't let you have NULL values in those fields (you can't have a null value in a Primary Key, for example)

Have you tried doing an INSERT with a NULL UserID from Management Studio, to see whether the problem is in the LINQ side, or the SQL Server side? Also, if it works from Management Studio, try turning on logging for LINQ, and append the actual SQL query being run, that'll help diagnose.

Answer 2

So the first and only insert of a NULL UserId fails? So you should be able to set up a simple repro case and post the LINQ code?

Answer 3

Ok the problem wasn't actually the insert statement. It was the fact that I am not returning any records when the UserId is NULL and thus inserting the same records again. So... SQL server was doing the correct thing :)

However something I found something weird when dealing with null values in LINQ. I had to change the following

return (from dm in Context.DesktopModules
     join qvu in Context.QuickViewUserModules on dm.DesktopModuleID equals qvu.QuickViewModuleId
     where qvu.UserId == (userId == Null.NullInteger ? null : (int ?)userId)
     orderby qvu.SortOrder, dm.ModuleName ascending
     select dm).ToList();

to this

if(userId == Null.NullInteger)
{
    return (from dm in Context.DesktopModules
     join qvu in Context.QuickViewUserModules on dm.DesktopModuleID equals qvu.QuickViewModuleId
     where qvu.UserId == null
     orderby qvu.SortOrder, dm.ModuleName ascending
     select dm).ToList();
}
else
{
    return (from dm in Context.DesktopModules
     join qvu in Context.QuickViewUserModules on dm.DesktopModuleID equals qvu.QuickViewModuleId
     where qvu.UserId == userId
     orderby qvu.SortOrder, dm.ModuleName ascending
     select dm).ToList();
}

It seems that LINQ doesn't work when (int) & (int ?) are mixed together.

Answer 4

I always struggle with null values, too, and end up casting back and forth between nullable and non-nullable types. There is one other issue related to FKs and NULL that I am aware off, which you may run into when it comes to deletions:

http://blogs.msdn.com/bethmassi/archive/2007/10/02/linq-to-sql-and-one-to-many-relationships.aspx