D LINQ : How to Map Columns Which Auto Generate Values At the Database
2009-05-11
I have being working on a software application made on .Net and recently my client asked me to use D LINQ instead of SQL.
D LINQ has great benefits loaded! As I started working with DLINQ I started to know that preventing SQL injection is not a headache anymore and misspelled SQL queries will not trouble agian at run time because DLINQ generates all necessary SQL inside the framework!
I chose to use annotations inside the class instead of using separate xml file. Following is a part of the first class I ported to D LINQ.
</p> <table(Name:="tbl_Process")> _ Public Class Process <column(Name:="ProcessID", DbType:="Int", isprimarykey:=True)> _ Public ProcessID As Integer <column(Name:="BusinessProcessID", DbType:="varchar(50)")> _ Public BusinessProcessID As String <column(Name:="ProcessText", DbType:="varchar(50)", isprimarykey:=True)> _ Public ProcessText As String ... ...
ProcessID column is the primary key of my database table tbl_Process.
Important Point: I use database to auto-generate values for the primary key column (integer value incremented by one).
So when I run the application, It gave me this unexpected error:
Cannot insert explicit value for identity column in table ‘tbl_Process’ when IDENTITY_INSERT is set to OFF.
Basically, the IDENTITY_INSERT when using the database to auto generate value for a field but when I ran a SQL insert statement at the database end it worked fine!
After some tough time I found out the solution for this problem!
When we use an auto-generate field in a data table we have to specifically mention it in the matching field in the appropriate class.
The code to state this is:
Syntax:
IsDbGenerated:=True
This has to be added to the annotation added for the specific column like:
</p> <column(Name:="ProcessID", DbType:="Int", isprimarykey:=True, IsDbGenerated:=True)> _ Public ProcessID As Integer
Now D LINQ can understand that the field value is auto-generated by the database!
Was this post helpful to you? How can I improve? – Your comment is highly appreciated!
Cassian Menol Razeek