• Home
• Great Quotes
• About

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

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.