Error "The primary key column of type 'DateTime' cannot be generated by the server."

Question

I have a composite primary key (Int and DateTime) on one of my tables. When I try to add a new record using LINQ (with the DateTime field set to "AutoGeneratedValue = true", and getdate() as default value on the server), I get the following errors:

The primary key column of type 'DateTime' cannot be generated by the server.

Any idea why this is? I believe it has worked before but I can't make out what would cause it to stop working. Unfortunately there is little specific information about this kind of error.

Also, manually committing a row through SQL Server Management Studio works fine and is correctly autogenerating the datetime value.

Edit - Got it working - Must be a bug in LINQ to SQL: I got it working if I set "IsPrimaryKey" for the DateTime to false within the DBML while keeping the composite key on the database side.

So this works:

• SQL Server: Composite Primary Key (Id INT, MyDate DATETIME - AUTOGENERATED)
• DBML: Mark only Id as Primary Key. Mark MyDate as Autogenerated, but not as Primary Key.

I think that's a bug in LINQ to SQL.

Answer 1

You need to set the default of the field in SQL Server to self-generate. For DateTime fields this is normally GETDATE().

Edit:

Apart from AutoGeneratedValue = true you also need to set Auto-Sync to OnInsert.