I have a table that looks like this:
ClientID is the only column that is in the table. UserID is the FK for the primary key of different tables.
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 interested, the reason why I have all these assignments is because I translated between my POCO domain objects and the objects created by linq. In the case of this exception, it takes the path of the else statement, creating a new client.
You can see that I am NOT touching the ClientID property, which is the ~ only ~ identity column in the table.
" " ", IDENTITY_INSERT " "?
, , stacktrace:
System.Data.SqlClient.SqlException unhandled
= " " " IDENTITY_INSERT OFF."
Source = ".NET SqlClient Data Provider" ErrorCode = -2146232060 = 16
LineNumber = 1 Number = 544
= "
Server =" 192.168.168.190" = 1
: System.Data.SqlClient.SqlConnection.OnError(SqlException , Boolean breakConnection) System.Data.SqlClient.SqlInternalConnection.OnError(SqlException , Boolean breakConnection) System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String, DbAsyncResult) System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) System.Data.SqlClient.SqlCommand.ExecuteNonQuery() System.Data.Linq.SqlClient.SqlProvider.Execute( , QueryInfo queryInfo, IObjectReaderFactory factory, Object [] parentArgs, Object [] userArgs, ICompiledSubQuery [] subQueries, Object lastResult) System.Data.Linq.SqlClient.SqlProvider.ExecuteAll( query, QueryInfo [] queryInfos, IObjectReaderFactory factory, Object [] userArguments, ICompiledSubQuery [] ) System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute( ) System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject ) System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject ) System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) System.Data.Linq.DataContext.SubmitChanges() DomainModel.Repository.Concrete.SqlClientRepository.InsertClientByUsername(String , Client clientInfo)