Linq to Sql update creates duplicate record

Question

I'm using linq to sql in my data layer where I have added a "settings" table to the regular asp_user table. In this settings table I keep several records of which one is the prefered Unit (a foreign key to the Units table).

Now when this prefered unit needs to change (say from liter to kilogram), I do a call to my UnitService to get the correct unit instance and set my setting.PreferedUnit to this instance. This creates a new (duplicate) item in the database (there are now two kilogram records in my Units table). This is of course not what I intended.

I have the feeling I'm doing a bunch of fundamental stuff wrong

UserSetting setting = AccountService.GetUserSetting(user.Identity.Name);
if (setting.PreferedUnitId != unitId)
  setting.Unit = UnitService.GetUnitById(unitId);
AccountService.SaveuserSetting(setting);

Answer 1

What does AccountService.SaveuserSetting do? It's probably doing an unnecessary InsertOnSubmit.

Answer 2

You probably need to do a DataContext.Units.Attach(unit) to let Linq to SQL know that your unit object already exists and doesn't need to be inserted. Note that I'm assuming that your Units table is exposed as a Units property on the DataContext.

I'm speculating here, but it appears that your AccountService is using a different DataContext to your UnitService so you're actually in the disconnected objects scenario. This blog post might make things a bit clearer