linq-sql insert is ignoring sql server default value for the primary key

Question

I have a table that the primary key field has a default value of newid() --> that generates a unique id every time a record gets inserted. when I try to insert a record directly in the database from any sql management tool, it works. but when I try to use C# linq-sql insert it gives my the below error:

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails. The statement has been terminated.

why is linq ignoring the default value of the field, and is there any alternative way of doing this, given that I don't want the normal sequential int id in the primary key below is the c# code for the insert:

 databaseDataContext db = new databaseDataContext();
        order c = new order();

        c.orderName = "Untitled";
        c.orderStatus = "New";
        db.orders.InsertOnSubmit(c);
        db.SubmitChanges();

Try updating your model first and then if it's not working, please provide us with a peice of code that throws the exception. — Ramy Moussa
– Ramy Moussa, Commented Mar 9, 2015 at 20:25
Yes, since you are using a GUID anyway, generate it in your C# code and assign it your ID field before the insert. — dbugger
– dbugger, Commented Mar 9, 2015 at 20:26
@dbugger makes a great suggestion - I suspect LINQ-SQL is setting passing the NULL ID to SQL which would prevent SQL from using the default. In any case, generating the key yourself means you don't have to go back to the DB to find out what it was - you can use it in relationships, etc. — n8wrl
– n8wrl, Commented Mar 9, 2015 at 20:32

TamerM · Accepted Answer · 2015-03-10 06:40:42Z

3

The suggestion provide by @dbugger is good enough. However, in case you still need it to be generated in SQL, make sure you specify that the ID column (primary key field) has the IsDbGenerated attribute along with the IsPrimaryKey:

[Column(IsDbGenerated = true, IsPrimaryKey = true)]

answered Mar 10, 2015 at 6:40

TamerM

7729 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

linq-sql insert is ignoring sql server default value for the primary key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related