LINQ to SQL insert primary key index

Question

[Column(Name = "id", IsPrimaryKey = true, CanBeNull = false)] 
public string id { get; set; }

Error:

Cannot insert the value Null into column id, column does not allow nulls

If I set it to an identity column and make a few changes in my [Column attributes] I end up with a IDENTITY insert not enabled error, what should I do? Do I need my primary key column to be an identity as well?

It's just a simple table with +1 increment on primary key

public partial class linqtest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            MyContext db = new MyContext("Server=(local);Database=Test;User ID=admin;Password=pw");
            Child c = new Child();
            c.name = "joe "+DateTime.Now;
            db.parents.InsertOnSubmit(c);
            db.SubmitChanges();
        }
    }

    public class MyContext : DataContext
    {
        public static DataContext db;
        public MyContext(string connection) : base(connection) { }
        public Table<Parent> parents;
    }

    [Table(Name="parents")]
    [InheritanceMapping(Code = "retarded", Type = typeof(Child), IsDefault=true)]
    public class Parent
    {
        [Column(Name = "id", IsPrimaryKey = true, CanBeNull = false, DbType = "Int NOT NULL IDENTITY", IsDbGenerated = true)] /* I want this to be inherited by subclasses */
        public int id { get; set; }

        [Column(Name = "name", IsDiscriminator = true)] /* I want this to be inherited by subclasses */
        public string name { get; set; }
    }

    public class Child : Parent
    {

    }

why are you setting the primary key?I mean doesn't whatever you are using for your database provide you primary keys? — Bastardo
well, if you have a database and some tables in it and using let's say Sql Server or Oracle etc. you dont have to. You can set this up in these programs and they provide primary keys in your table. — Bastardo

marc_s marc_s · Accepted Answer · 2011-04-12T21:12:59

If I just define my ID column as IsPrimaryKey = true and IsDbGenerated = true, everything works just fine:

[ColumnAttribute(Storage="_ID", AutoSync=AutoSync.OnInsert, 
 DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int ID
{....}

Then, in code - when I create a new entity, I just assign the entity an arbitrary ID - I typically tend to use negative numbers:

MyEntity entity = new MyEntity { ID = -55 };

MyContext.MyEntities.InsertOnSubmit(entity);
MyContext.CommitChanges();

After the call to .CommitChanges(), the new entities are stored in the database table, and the actual, real ID values (INT IDENTITY) are being set and reflected on my entity object - i.e. after the call, I get back the real ID that the database has given my new entity.

So my question is: where is your problem then?? It seems to work just fine....

LINQ to SQL insert primary key index

1 Answers