Linq to SQL insert with primary key as tinyint set to auto-increment gives error message

Question

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

This is more of an answer than a question because I've been searching for this on stack and couldn't find any question/answer to help.

Basically, I was trying to do an insert (below)

public void AddTask(Task task)
{
    TwoDooDataContext db = new TwoDooDataContext();
    db.Tasks.InsertOnSubmit(task);
    db.SubmitChanges();
}

And my primary key was set Identity true but the data type was set to TinyInt...see class attributes below

ColumnAttribute(Storage="_ID", AutoSync=AutoSync.OnInsert, 
DbType="TinyInt NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]

Doing this caused the error message:

the primary key column of type 'tinyint' cannot be generated by the server

So I couldn't figure this out until I could this link... http://connect.microsoft.com/VisualStudio/feedback/details/402060/accessing-a-primary-key-id-when-its-declared-as-a-tinyint-using-linq

It's a known issue but not fixed - I'm using SQL 2008 Express and VS Web Developer 2010 Express.

My fix was just to change the data type to Int and everything works. Hope this helps incase anyone else is looking for solution.

And actually a question - What's the best data type to use in my case? Should I keep it as Int or change it even though it's working? Thanks

Answer 1

Database Administrators has a nice discussion of when to use TINYINT over INT although I don't think it applies in your case. If using an int gets you past the known issue, you'd need a very good reason to change it. I'd leave it as is.