What parameters do I need to put into InsertOnSubmit(...) if one or more of my columns is an identity column?

Question

My table was created with

CREATE TABLE orgs ( orgid int NOT NULL identity(1,1), orgname nvarchar (MAX) NOT NULL, PRIMARY KEY (orgid) );

and my C# class that mirrors it is

[Table(Name = "orgs")]
public class Organization
{
    public Organization()
    {

    }

    [Column(IsPrimaryKey=true)]
    public int orgid { get; set; }
    [Column]
    public string orgname { get; set; }
}

However, when I issue the calls

        PD.orgs.InsertOnSubmit(new Organization { orgname = newOrgName });

        try
        {
            PD.SubmitChanges();
        } // try submitting to db
        catch (Exception e)
        {
            return Content(e.Message, "text/html");
        }

I learn that

Cannot insert explicit value for identity column in table 'orgs' when IDENTITY_INSERT is set to OFF.

This confuses me because I thought it was equivalent to issuing the direct SQL command

INSERT INTO orgs (orgname) VALUES ('something');

and yet I don't get an error when I do that.

Any ideas how I can fix this?

score 0 · Answer 1 · answered May 13 '15 at 15:16

0

[Table(Name = "orgs")]
public class Organization
{
    public Organization()
    {

    }


    [Column(IsDbGenerated=true)]
    public int orgid { get; set; }
    [Column]
    public string orgname { get; set; }
}

change ur class like this and check

answered May 13 '15 at 15:16

Sachu

7,555
7
55
94

That works! But don't I need `[Column(IsPrimaryKey = true, IsDbGenerated = true)]` or is the `IsPrimaryKey=true` unnecessary? – Fired from Amazon.com May 13 '15 at 15:20
@downvoter can u pls tell what the issue here? – Sachu May 13 '15 at 15:21
It's still the primary key. Note the documentation says "If the column holds primary key values and you designate IsDbGenerated as true, you should also add the DbType property by using the IDENTITY modifier." – stuartd May 13 '15 at 15:21
@stuartd since it is orgid int NOT NULL identity(1,1) no need to specify primary key..it will be autogenerated and will be primary – Sachu May 13 '15 at 15:23
@Sachu the IsPrimaryKey docs say "Assuming an entity class, you must provide at least one member with this attribute, and it must be mapped to the primary key or a unique key in the corresponding table or view. Failure to do this prompts LINQ to SQL to consider instances of the class as read-only for submitting changes to the database." – stuartd May 13 '15 at 15:25
@stuartd ok agree but [Column(IsDbGenerated=true)] statement is needed else class will consider it as off thats why he is getting the error – Sachu May 13 '15 at 15:26
@FiredfromAmazon.com if it solved ur issue can u please upvote it? – Sachu May 13 '15 at 15:30
@Sachu yes, I just upvoted you. Thanks for your help. – Fired from Amazon.com May 13 '15 at 15:36

What parameters do I need to put into InsertOnSubmit(...) if one or more of my columns is an identity column?

1 Answers1