1

I'm trying to insert new records into a SQL CE 4 database with LINQPad and having problems with the identity problem of a table. Let's say I have this simple table for instance:

PEOPLE
Id int IDENTITY(1,1) NOT NULL,
Name nvarchar(100) NOT NULL

I may be doing things the wrong way but I tried this in LINQPad

People person = new Person { Name = "Bob" };
People.InsertOnSubmit(person);
SubmitChanges();

But I get a SqlCeException stating

"The colum cannot be modified. [ Column name = Id ]"

I can insert a record with SQL just fine, that works with no errors from SQL CE or its data provider and SQL CE sets the Id column for me which is what I'm wanting

INSERT INTO PEOPLE (Name) VALUES ('Bob');

Is there another step that I'm missing? I'm not even sure if its a LINQPad issue but thought I'd ask anyways since that's what I'm trying this code with right now.

Jeff LaFay
  • 12,882
  • 13
  • 71
  • 101

2 Answers2

2

What do you get if you run this in LinqPad

(from dm in this.Mapping.GetTable(typeof(People)).RowType.DataMembers 
    select new  { dm.DbType, dm.Name, dm.IsPrimaryKey , dm.IsDbGenerated }
 ).Dump();

In particular, as I understand it, IsDbGenerated should be true for the id column.

I have a SQL CE 3.5 data file that I have used previously in LinqPad and looking at the SQL generated for inserts it does not mention id

sgmoore
  • 15,694
  • 5
  • 43
  • 67
  • Nice pointer! Id shows as primary key but IsDbGenerated is False. So is that something I can change in the SQL file or something that needs to be set programatically in LINQPad? Of course it would be preferable to alter the file but I'm not sure if it's possible or not. – Jeff LaFay May 27 '11 at 17:54
  • You shouldn't need to do anything in LinqPad. If you are letting Linqpad build the datacontext itself, then there must be something wrong without your database schema (possibly id is not set as a identity/auto-incremented column). If you are using your own pre-defined datacontext, then there must be an error in it (which usually starts with the dbml file) – sgmoore May 27 '11 at 18:04
  • Shouldn't I only have to create the column with IDENTITY(1,1) to make it auto increment? That's what I've read so far. I'm not sure if autoincrement is a keyword for SQL CE. I'm using LINQPad's datacontext so it must be the schema. Just need to figure that out. – Jeff LaFay May 27 '11 at 18:12
  • I just tried CREATE TABLE x ( Id int IDENTITY(1,1) NOT NULL , CONSTRAINT PK_xID PRIMARY KEY (id) ) and CREATE TABLE x2 ( Id int IDENTITY(1,1) NOT NULL PRIMARY KEY ) and both of these result in id having IsDbGenerated true. (This is SQL CE3.5) – sgmoore May 27 '11 at 18:22
  • 1
    Aggh. Just remembered something. I forgot to mention that I am using the beta of LinqPad version 4.34 (from http://www.linqpad.net/beta.aspx) and I just tried this on an older version (2.31) and with it IsDbGenerated is false. So it looks like this is a bug with Linqpad which has already been fixed. – sgmoore May 27 '11 at 19:53
  • 1
    Yes, it's a bug in the current release that I'm using (4.31) and the beta 4.35.1 has the correct fix. Thanks so much! – Jeff LaFay May 31 '11 at 12:32
1

This is probably the issue with dbml file. Check whether the column is marked as identity(or whatever this is called in L2S). The thing is that Id cannot appear in the insert query.

kubal5003
  • 7,186
  • 8
  • 52
  • 90
  • I created the table myself and set it as identity as I show above. If you also look at what I provided for information, I'm not setting ID. The entity automatically sets it to zero (int's default) since it's not nullable. – Jeff LaFay May 27 '11 at 17:25
  • I was talking about the DBML which is an xml file that describes mappings in LinqToSQL. LinqToSql generates queries to your database based on the mappings provided there. In this case you were not aware of it since it was autogenerated by LinqPad. Still this was the case. – kubal5003 May 31 '11 at 23:43
  • I'm sure that LINQPad generates all kinds of temporary files to maintain its flexibility and it appears that the DBML is one of them. I can't really fix my issue by editing a temporary file. It turned out that it wasn't anything I was doing wrong and the bug is fixed in 4.35 beta. So I'm happy. Thanks for the info on DBML settings though, it's much appreciated. – Jeff LaFay Jun 01 '11 at 13:54