Why am I getting “Cannot insert explicit value for identity column” in LINQ to SQL when I'm not specifying a value for an identity column?

5
votes

I have a table that looks like this: alt text

ClientID is the only identity column I have in the table. UserID is a FK to a different tables primary key.

Here is my Linq to SQL insert code:

public void InsertClientByUsername(string username, Entities.Client clientInfo)
{

    using (LinqModelDataContext db = new LinqModelDataContext())
    {

        var existingClient = (from client in db.Clients
                              join ext_usr in db.User_Extendeds on client.UserID equals ext_usr.FriendlyUserID
                              join asp_usr in db.aspnet_Users on ext_usr.UserID equals asp_usr.UserId
                              where asp_usr.UserName.ToLower().Equals(username)
                              select client).SingleOrDefault();

        if (existingClient != null)
        {
            existingClient.Address1 = clientInfo.Address1;
            existingClient.Address2 = clientInfo.Address2;
            existingClient.City = clientInfo.City;
            existingClient.CompanyName = clientInfo.CompanyName;
            existingClient.CountryID = clientInfo.CountryID;
            existingClient.FaxNumber = clientInfo.Fax;
            existingClient.FirstName = clientInfo.FirstName;
            existingClient.LastName = clientInfo.LastName;
            existingClient.MailingAttention = clientInfo.Attention;
            existingClient.PhoneNumber = clientInfo.PhoneNumber;
            existingClient.StateID = clientInfo.StateID;
            existingClient.ZipCode = clientInfo.Zip;

        }
        else
        {
            int userID = (from ext_usr in db.User_Extendeds
                          join asp_usr in db.aspnet_Users on ext_usr.UserID equals asp_usr.UserId
                          where asp_usr.UserName.ToLower().Equals(username)
                          select ext_usr.FriendlyUserID).SingleOrDefault();

            Client newClient = new Client();
            newClient.UserID = userID;
            newClient.Address1 = clientInfo.Address1;
            newClient.Address2 = clientInfo.Address2;
            newClient.City = clientInfo.City;
            newClient.CompanyName = clientInfo.CompanyName;
            newClient.CountryID = clientInfo.CountryID;
            newClient.FaxNumber = clientInfo.Fax;
            newClient.FirstName = clientInfo.FirstName;
            newClient.LastName = clientInfo.LastName;
            newClient.MailingAttention = clientInfo.Attention;
            newClient.PhoneNumber = clientInfo.PhoneNumber;
            newClient.StateID = clientInfo.StateID;
            newClient.ZipCode = clientInfo.Zip;

            db.Clients.InsertOnSubmit(newClient);

        }

        db.SubmitChanges();
    }
}

In case you are curious, the reason I have all those assignments is because I'm translating between my POCO domain objects and the linq generated objects. In the case of this exception, it is taking the path of the else statement, creating a new client.

You can see that I'm NOT touching the ClientID property which is the ~only~ identity column in the table.

Why am I getting the "Cannot insert explicit value for identity column in table 'Client' when IDENTITY_INSERT is set to OFF?

In case it is useful, here is my stacktrace:

System.Data.SqlClient.SqlException was unhandled by user code
Message="Cannot insert explicit value for identity column in table 'Client' when IDENTITY_INSERT is set to OFF."
Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16
LineNumber=1 Number=544
Procedure=""
Server="192.168.168.190" State=1
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item) at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item) at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges() at DomainModel.Repository.Concrete.SqlClientRepository.InsertClientByUsername(String username, Client clientInfo)

7
c#linq-to-sql
You could use SQL Profiler to look at the generated SQL. But there isnt much doubt that L2S is generating a value. Have a look in the genned code (Project|Show All files) to see whether the metadata for the ClientId column really has the appropriate 'identity marker' (it musnt, or there must be some file version confusion ...) – Ruben Bartelink
@Ruben Bartelink, for some reason it was a weird bug in my generated code. I deleted my data context and rebuilt it, and this time it worked. Strange. – FalconKick
I had the same problem... SqlDesigner issue I guess. – Sumtraveller

7 Answers

4
votes

add StoreGeneratedPattern="Identity" in the .edmx file (view in txt editor)

1
votes

From here

Try setting "Auto Generated Value" to false

1
votes

I also had the same problem and deleting the table and reading it fixed it. I don't know about he solution not being pretty, it certainly did the trick and was very quick. Its ugly in that is a bug in Linq-to-Sql and Visual Studio 2008 (in my case, it could be a bug in other versions too), but its at least a livable bug.

1
votes

I had this same issue, but wiping out the entity and importing it back didn't do the trick for me which caused be to have to dig even deeper. The real issue here is that under the SSDL content of your entity diagram there is a missing attribute (StoreGeneratedPattern="Identity") that should be on the ID property of your entity. Once you add this attribute you should be all set.

Example:

<EntityType Name="TABLENAME">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
</EntityType>
1
votes

I deleted the table from *.dbml in visual studio and inserted it again. the above problem is solved!

0
votes

As @Martin says, I've had the same problem though I didn't have to delete the whole diagram to fix it. All I had to do was force the diagram to regenerate the code. This is usually just a matter of changing something on the diagram and changing it back. In more severe cases, I've had to restart VS or even reboot to get the designer to regenerate the code.

0
votes

Try setting Read Only to true.