LINQ To SQL ignores exception exception exception and continues

Question

I have one table in a database called Users

  Users
 ------
 ID (PK, Identity)
 Username (Unique Index)

I have a unique index in the Username table to prevent duplication. Then I list the collection and create a new user in the database for each item.

What I want to do is simply insert a new user and ignore the exception if the only key restriction is violated (in this case, the entry is obviously duplicated). This is to avoid creating where no queries exist .

First, will it be more efficient, or should my embed code check for duplicates? I am more attached to a database with this logic, as this prevents duplicate data from being inserted into another type of client.

My other problem is with LINQ To SQL. I have the following code:

public class TestRepo { DatabaseDataContext database = new DatabaseDataContext(); public void Add(string username) { database.Users.InsertOnSubmit(new User() { Username = username }); } public void Save() { database.SubmitChanges(); } } 

And then I iterate over the collection and insert new users, ignoring any exceptions:

 TestRepo repo = new TestRepo(); foreach (var name in new string[] { "Tim", "Bob", "John" }) { try { repo.Add(name); repo.Save(); } catch { } } 

The first time this is done, I have three users in the table. If I delete the second and run this code again, nothing will be inserted. I expected the first insert to fail, and the second to succeed (since I just deleted this item from the database), and the third to fail.

What seems to happen when after a SqlException (although the loop continues the iteration), all subsequent inserts fail - even if there is no row in the table that could cause a unique violation.

Can anyone explain this?

PS The only workaround I could find was to create an instance of the repo every time before inserting, then it worked exactly as excluded - indicating that this was something related to LINQ To SQL DataContext.

Thanks.

Answer 1

In the second insertion, the dataContext will try to insert the first objects again, since it sits in the identification card for that dataContext (your desire to insert "Tim" is still waiting after the first catch).

In the first cycle, you insert “Tim” in the second “Tim” and “Bob” in the third “Tim”, “Bob” and “John” Thus, you do not insert only “Bob” in the second cycle, and that’s why it doesn’t work.

You can try to remove the bad name from the DataContext in cach, but in any case several messages in the same data context are very bad ideas (I got burned on this).

Answer 2

Try calling DataContext.Refresh in your DataContext between test runs.

I think you hanged on the internal caching in Linq-to-Sql, so re-creating the DataContext acts like work, when you are new to the DataContext, the new object cache will be empty.

Answer 3

You should check for availability using the try try is bad trick.

 foreach (var name in new string[] { "Tim", "Bob", "John" }) { var u = from users in db.Users where users.username == name select users; if (u.Count() == 0) { User user = new User(); user.name = name; db.Users.InsertOnSubmit(user); db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict); } } 

Answer 4

It's hard to say if your method is effective. This will depend on how often you get duplicate accounts. If you have a very high speed, you lose speed, since most likely it will take more time to eliminate the exception than to check for a record.

If you want to increase your speed, create a stored procedure for checking and pasting if it is not in the database.