The null value cannot be assigned to a member with type System.Int64 which is a non-nullable value type

Question

I'm getting the following error in my MVC2 app using Linq to SQL (I am new to both). I am connected to an actual SQL server not weird mdf:

System.InvalidOperationException The null value cannot be assigned to a member with type System.Int64 which is a non-nullable value type

My SQL table has a column called MessageID. It is BigInt type and has a primary key, NOT NULL and an IDENTITY 1 1, no Default

In my dbml designer it has the following declaration for this field:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_MessageId", AutoSync=AutoSync.OnInsert, DbType="BigInt NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public long MessageId
{
    get
    {
        return this._MessageId;
    }
    set
    {
        if ((this._MessageId != value))
        {
            this.OnMessageIdChanging(value);
            this.SendPropertyChanging();
            this._MessageId = value;
            this.SendPropertyChanged("MessageId");
            this.OnMessageIdChanged();
        }
    }
}

It keeps telling me that null cannot be assigned - I'm not passing through null! It's a long - it can't even be null!

Am I doing something stupid? I can't find a solution anywhere!

I made this work by changing the type of this property to Nullable<long> but surely this can't be right?

Update: I am using InsertOnSubmit. Simplified code:

public ActionResult Create(Message message)
{
    if (ModelState.IsValid)
    {
       var db = new MessagingDataContext();
       db.Messages.InsertOnSubmit(message);
       db.SubmitChanges(); //line 93 (where it breaks)
    }
}

breaks on SubmitChanges() with the error at the top of this question.

Update2: Stack trace:

   at Read_Object(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at Qanda.Controllers.MessagingController.Ask(Message message) in C:\Qanda\Qanda\Controllers\MessagingController.cs:line 93

Update3: No one knows and I don't have enough clout to offer a bounty! So continued on my ASP.NET blog. Please help!

Answer 1

The value types you have defined in your Result class need to be setup as nullable.

In your case use int64?

Check out the object code in your designer.cs file that was generated by the designer, if you are using it.

Modify this code and place it in a partial class so the designer will not over write the code.

Answer 2

It has taken a while but I have discovered this was happening and thought I'd share. It was because the table had a trigger on insert. I've wrote about it in more detail here optimistic concurrency exception with triggers. Although this is with entity framework, I'm still sure it is the trigger causing my dismay from the start