LINQ to SQL exception when attempting to insert a new entry in a database table

Question

I'm trying to insert a user in my database via LINK to SQL and I'm getting an exception:

Cannot insert the value NULL into column 'UserID', table 'mydatabase.developer.Users'; column does not allow nulls. INSERT fails. The statement has been terminated.

I've marked the UserID as the primary key in my Users table and I was expecting that the SQL Server will automatically generate a PK when I try to insert a new user in the table.

I pretty much copied and pasted the example from Pro ASP.NET MVC Framework Chapter 4 Section on "Setting Up LINQ to SQL." Everything is in order... my database has a Users table with a UserID (PK) column and a Name column (both non-nullable), below is the class corresponding to the database table:

public class User
{
    [DisplayName("User ID")]
    [Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
    internal int UserID { get; set; }

    [DisplayName("Name")]
    [Column]
    public string Name{ get; set; }
}

I also have a repository class which allows me to modify the database:

public class UsersRepository : IUsersRepository
{
    private DataContext database;
    private Table<User> usersTable;

    public UsersRepository(string connectionString)
    {
        database = new DataContext(connectionString);
        usersTable = database.GetTable<User>();
    }

    public void AddUser(User user)
    {
        usersTable.InsertOnSubmit(user);
        try
        {
            database.SubmitChanges();
        }
        catch (Exception e)
        {
            var msg = e.Message;
        }
    }
    //...
}

IUsersRepository ur = new UsersRepository(connectionString);
ur.AddUser(new User{Name = "Joe"});

I've also tried setting the UserID to -1, 0 and 1, but I get the same exception!

ur.AddUser(new User{UserID = -1, Name = "Joe"});
ur.AddUser(new User{UserID = 0, Name = "Joe"});
ur.AddUser(new User{UserID = 1, Name = "Joe"});

Does anybody know what may be happening here? I'm really baffled by this: what could cause the SQL server not generating the primary key? Is there any way I can inspect the insert statement and verify what is actually being sent to the server?

Same happened to me on winforms. Did not find the solution and ended up using SQL strings with Context.ExecuteCommand(command1) which works all the time — Mathieu
@Mathieu I figured it out... auto-increment must be set to true. It seems quite obvious, but what's not so obvious is what auto-increment is called in SQL Server... turns out it's called "identity" and setting the identity causes auto-increment so it works like a charm now! — Kiril

mfanto mfanto · Accepted Answer · 2011-02-21T22:26:05

Is your primary key an integer? You need to set the identity specification: enter image description here

If you are using something like a uniqueidentifier, you need to generate on the client with

User user = new User();

user.Id = Guid.NewGuid();

LINQ to SQL exception when attempting to insert a new entry in a database table

2 Answers