Linq2SQL working on Console Applicaton, but not working on Windows Service

It was difficult to choose correct title, but here is the problem:

I've an application with simple logic, that gets data from TFS, iterates over "item开发者_如何学Cs" and updates local DB with these "items". Here is a code snippet for updating the row:

using (ResultDBDataContext local_db= new ResultDBDataContext())
{
DBTestResult dbRes = local_db.DBTestResults.getByPrimaryKey(args_supplied);
if (dbRes == null) {
  dbRes = new DBTestResult(){//set properties}
  local_db.DBTestResults.InsertOnSubmit(dbRes);
}
else{
  dbRes.isDirty = true;
  dbRes.otherPropertis = set_to_some_values;
}
local_db.SubmitChanges();
}

This is working in Windows Console program (imported >100K rows like this), which proves that logic is correct. But when I put same logic to Windows Service (against same data), after a while (<2K rows) I get an SQL exception saying "can't insert duplicate key into the table". Note that I do SubmitChanges() after updating/inserting each row

I think this is somehow related on the followings, but not able to find out the reasons:

• How Windows Services handle DB connectios. (Do they pool? Do they cache data? etc.)
• Linq2SQL Dataconext handling it's update objects. (What properties of Linq2SQL Datacontext make it behave differently under Windows Services?)

Any ideas/suggestions/links/tutorials/articles on how to resolve this issue or on finding out more information about the problem are greatly welcome.

Thanks.

Update: The data coming from TFS, may violate the database's primary key constraint - but since I'm inserting/updating one row at a time and committing changes, the last entry for the same primary key will reside in database.

Any ideas/suggestions/links/tutorials/articles on how to resolve this issue or on finding out more information about the problem are greatly welcome.

This sounds to me like it's not a problem with the service side of the code.

I think you need to log/inspect this error behaviour further - just try logging which primary key(s) this is occurring for - it may be that you'll notice some error in your getByPrimaryKey implementation that way.

Also check that your service is single threaded - i.e. that there can't be two instances executing simultaneously - that could certainly lead to an error.

What is triggering your service to start it's operation? It sounds like your process is runnning a second time which is causing your data to be processed twice rather than once when running it as a console application.

For example, if you are using the FileSystemWatcher to let you know when new files arrive to process, the watcher will fire multiple events as the file is created, and added to (updated).