LINQ to SQL in C# issue with InsertOnSubmit

Question

I have my model defined in dbml file. I have a Car entity to which I want to insert data through Form. The form use this block of code:

 private void button1_Click(object sender, EventArgs e)
    {
            Car CarToCreate = new Car();
            CarToCreate.Name = newCarNameTextBox.Text;
            CarToCreate.CarClass = newCarClassComboBox.SelectedItem.ToString();
            CarToCreate.PricePerDay = Convert.ToDecimal(newCarPriceTextBox.Text);
            CarToCreate.Capacity = Convert.ToInt32(newCarCapacityTextBox.Text);
            CarToCreate.RegistrationNumber = newCarRegNumberTextBox.Text;
            CarToCreate.Description = newCarDescriptionTextBox.Text;
            CarToCreate.CarState = "Available";

            Context.Cars.InsertOnSubmit(CarToCreate);
            Context.SubmitChanges();

            CarModifiedEvent();
            this.Close();
    }

First two records are inserted successfully. When I try to insert third row two previous are deleted (Context.Cars.InsertOnSubmit line). I was able to trace queries in SQL Profiler:

exec sp_executesql N'INSERT INTO [dbo].[Car]([Name], [CarClass], [CarState], [PricePerDay], [Capacity], [RegistrationNumber], [Description])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 varchar(8000),@p1 varchar(8000),@p2 varchar(8000),@p3 decimal(18,2),@p4 int,@p5 varchar(8000),@p6 varchar(8000)',@p0='b',@p1='B',@p2='Available',@p3=2.00,@p4=2,@p5='2',@p6='2'

exec sp_executesql N'DELETE FROM [dbo].[Car] WHERE ([CarID] = @p0) AND ([Name] = @p1) AND ([CarClass] = @p2) AND ([CarState] = @p3) AND ([PricePerDay] = @p4) AND ([Capacity] = @p5) AND ([RegistrationNumber] = @p6) AND ([Description] = @p7)',N'@p0 int,@p1 varchar(8000),@p2 varchar(8000),@p3 varchar(8000),@p4 decimal(18,2),@p5 int,@p6 varchar(8000),@p7 varchar(8000)',@p0=35,@p1='a',@p2='A',@p3='Available',@p4=1.00,@p5=1,@p6='1',@p7='1'

Why the first two records are just inserted and third record deletes all previous? Am I missing something?

Thank you!

Answer 1

Where do you define/initialize Context? It should be local to that code, otherwise you run the risk of concurrency problems (which may be the cause of the behavior you're seeing). Since this is a click event handler, it's reasonable to assume that this functionality is invoked when a user performs/invokes some kind of action in the interface. The approach for that should generally be:

1. User invokes action.
2. Action is processed and completed/committed.
3. UI responds to the user.

(Multi-threading aside, of course. But that's a whole host of other concurrency issues which you probably don't want/need to deal with at this point.)

However, you have this nebulous Context variable which exists outside of this scope. What other scopes are operating on this variable? You're not encapsulating your unit of work, which you should be doing.

Reduce the scope of your Context variable to only the action being executed in that request. Something like this:

private void button1_Click(object sender, EventArgs e)
{
    using(var context = new DbContext())
    {
        Car CarToCreate = new Car();
        CarToCreate.Name = newCarNameTextBox.Text;
        CarToCreate.CarClass = newCarClassComboBox.SelectedItem.ToString();
        CarToCreate.PricePerDay = Convert.ToDecimal(newCarPriceTextBox.Text);
        CarToCreate.Capacity = Convert.ToInt32(newCarCapacityTextBox.Text);
        CarToCreate.RegistrationNumber = newCarRegNumberTextBox.Text;
        CarToCreate.Description = newCarDescriptionTextBox.Text;
        CarToCreate.CarState = "Available";

        context.Cars.InsertOnSubmit(CarToCreate);
        context.SubmitChanges();
    }

    CarModifiedEvent();
    this.Close();
}

This would create the DB Context, use it, and dispose of it once it's done. Thus each "insert" in this case is its own isolated event, unaffected by other events.