Linq to SQL - Error handling

Question

I have a linq to sql statement that inserts records in the database. If there is a duplicate, it throws Primary key violation .

after this happens, whenever i try to execute any other statement, it repeatedly shows this error. Is this a problem of transaction not getting closed. Also how to handle transactions in a more reliable way in LINQ to SQL

Answer 1

Are you re-using the same DataContext? If so, don't do that - create a new DataContext for each logical operation and use TransactionScope to manage transactions

Answer 2

Why not check for a duplicate record before trying to save the record?

Throwing exceptions are very costly.

Answer 3

If your primary key field is an identity field, DO NOT populate it when you insert it into the database. For example, given a Customer table with the following structure:

Customer
========
Customer_ID (PK, identity)
Last_Name (varchar)
First_Name (varchar)
Middle_Initial (char)

this is possible:

public int CreateCustomer(string lastName, string firstName, string middleInitial) 
{
    using (DataContext dc = new DataContext())
    {
        Customer customer = new Customer();

        customer.Last_Name = lastName;
        customer.First_Name = firstName;
        customer.Middle_Initial = middleInitial;
        dc.Customers.InsertOnSubmit(customer);
        dc.SubmitChanges();

        return customer.Customer_ID;
    }   
}

The most likely reason for your error is that you are trying to access the entity object after the DataContext in which it was created has been destroyed. Using the above example, this will produce an error similar to the one you are probably receiving:

public int CreateCustomer(string lastName, string firstName, string middleInitial) 
{
    using (DataContext dc = new DataContext())
    {
        Customer customer = new Customer();

        customer.Last_Name = lastName;
        customer.First_Name = firstName;
        customer.Middle_Initial = middleInitial;
        dc.Customers.InsertOnSubmit(customer);
        dc.SubmitChanges();
    }   

    return customer.Customer_ID;   // <<-- Error occurs here
}

The reason the error occurs is due to the built-in change tracking of the LINQ engine. When the DataContext is created, any entity objects created within that DataContext are tied to that DataContext via a reference within the entity object. Once the DataContext falls out of scope, the reference is no longer valid and the contents of the entity object are no longer considered reliable to the LINQ engine.

As far as transaction handling is concerned, a transaction is created when the DataContext is created. When SubmitChanges is called, all changes are executed within the context of the DataContext's transaction, no matter how many entities/tables are involved.

Answer 4

Each DataContext instance tracks the instances of the mapping classes that it has seen. When you say: myDataContext.InsertOnSubmit(myCustomer); you are registering a customer instance with this DataContext. Later, when you say myDataContext.SubmitChanges();, the DataContext attempts to carry out all changes that it is tracking.

• If these changes fail - the DataContext does not stop tracking them and will attempt to make the change each time SubmitChanges is called.

In order to have a DataContext that is not tracking a record that it can't insert, you should abandon this instance and new-up another one.