DBML how to recover from violation of UNIQUE KEY on insert?

Question

I would like to find out how to recover from Unique Key Violation error in DBML on insert.

My scenario:

1. Create a new DBML mapped object (call it A) (possible to be a duplicate but cannot be determined before) and use InsertOnSubmit(A),
2. Then in context I call SubmitChanges(ConflictMode.ContinueOnConflict)
3. I catch the error and try to resolve all conflicts by OverwriteCurrentValues
4. When I retry SubmitChanges I got the same error

Code:

var new_date_row = new dimension_date(); 
    // two columns: datetime date, identity key
new_date_row.Date = new DateTime(2014, 1, 1);
db_context.dimension_dates.InsertOnSubmit(new_date_row);

try
{
    db_context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

db_context.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);
db_context.SubmitChanges(); // exception thrown here

As a result these are never resolved when I have a duplicated date inserted and the same exception is being thrown on the second SubmitChanges() call.

How to recover from the error to successfully call SubmitChanges() ?

I am looking for a solution to use inside more complex ETL processing code. This should run for multiple rows and each row with ten or more dimensions before I submit changes. I dont mind unique constraint errors - they mean that values I want to insert are already in the db and I am happy with that. Next steps here are surrogate key substitution for each dimension and insert or update for the fact table (not included in this example).

Answer 1

The preferred solution to this would be:

        var date = new DateTime(2014, 1, 1);
        if (!db_context.dimension_dates.Any(x => x.Date == date))
        {
            var new_date_row = new dimension_date();
            // two columns: datetime date, identity key
            new_date_row.Date = date;
            db_context.dimension_dates.InsertOnSubmit(new_date_row);
            db_context.SubmitChanges();
        }