Reputation: 133

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?

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)

Upvotes: 5

Answers (7)

BlueMonkMN

Reputation: 25601

Try setting Read Only to true.

Upvotes: 0

tazari

Reputation: 11

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

Upvotes: 1

Billy Logan

Reputation: 2863

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>

Upvotes: 1

Chris McKenzie

Reputation: 3851

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.

Upvotes: 0

William Triest

Reputation: 81

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.

Upvotes: 1