Cannot add an entity with a key that is already in use update operation

Question

I am creating small application in which i have used LINQ To SQL to perform all operation to database.

Now here i am giving the small part of my database structure please take a look.

So update language detail i am getting the object of login using the datacontext something like this.

XVDataContext Context = new XVDataContext ();

var myQuery = from objLogIn in Context.GetTable<LogIn>() where objLogIn.Emp_Id == nEmpId select objLogIn;

In nEmpId i will always have some value.

So it is not creating any problem in fact i am getting the required record from DB and storing it in objUser object using the following code.

LogIn objUser = myQuery.First<LogIn>();

Now to update LanguageDetail i am executing following code but it throws Exception when i execute SubmitChanges line.

Here is the code that i am executing to update.

LanguageDetail obj = new LanguageDetail();
foreach (string sLanguages in TextBoxLanguagesKnown.Text.Split('\n'))
{
       obj.Emp_Id = objUser.Emp_Id;
       obj.Language = sLanguages.Trim();
}
objUser.LanguageDetails[0] = obj;

Context.SubmitChanges();

I already read following links.

cannot add an entity with a key that is already in use

LINQ To SQL exception with Attach(): Cannot add an entity with a key that is alredy in use

Cannot add an entity with a key that is already in use (LINQ)

By reading the above links i found that i am doing some mistake in ID fields but still i am unable to resolve.

Please tell me the clear understanding of raising this issue and how can i resolve this.

EDIT:

I simply want to update LanguageDetail table.

When i try to add new object using following code it still throws exception.

objUser.LanguageDetail.Add(obj);

Answer 1

From my understanding you want to update the LanguageDetail entity in your database. In order to do so you have to do one of the following:

1. Retrieve the original LanguageDetail object based on its id, and update that object instead of creating a new one and assigning it the id of an existing object.
2. Attach the newly created object to your context instead of just giving a reference to it to your LanguageDetails collection. The exception you are seeing happens because the way linq to sql behaves is that it threats the obj as a new object that you want to insert and because of that it tries to insert it into the language details table.

Modifying your code like that should work:

Context.LanguageDetails.Attach(obj);
objUser.Employee_LanguageDetails[0] = obj;

Answer 2

You might want to add / remove languages for specific user by using following code.

var languages = TextBoxLanguagesKnown.Text.Split('\n');

// Removes deleted languages (first find all language details that are missing from the UI).
var deletedLanguages = objUser.LanguageDetails.Where(ld => !languages
    .Any(l => ld.Language == l.Trim())).ToArray();
foreach(var deletedLanguage in deletedLanguages)
{
    objUser.LanguageDetails.Remove(deletedLanguage);
    Context.LanguageDetails.DeleteOnSubmit(deletedLanguage);
}

// Adds new languages (then adds new language details that are not found in the database).
var newLanguages = languages.Where(l => !objUser.LanguageDetails
    .Any(ld => ld.Language == l.Trim())).ToArray();
foreach (string newLanguage in newLanguages)
{
    var languageDetail = new LanguageDetail
    {
        Emp_Id = objUser.Emp_Id,
        Language = newLanguage.Trim()
    };
    objUser.LanguageDetails.Add(languageDetail);
}

Context.SubmitChanges();