Best Practice Question (Primary Identity Keys)

Dec 26, 2010 at 5:19 PM

All of the tables in my application use autoincrement identity integer fields as their primary key. However, for some of the tables, I also want to ensure the other field values are unique. For example, I have a table of firstname/lastname pairs, and I don't want duplicate names.

When I edit the tables through a DataGrid, new records/rows are not committed to the database until a SaveChanges() action against the underlying EntityContext is called. The value of the key field is also not set until SaveChanges() is called (it defaults to 0).

In order to avoid duplicates, I call SaveChanges() after every new record is created (actually, I call it after every row edit is completed, but I don't think that matters for the question I'm raising). However, my app allows the user to have edits to other tables pending when the new record is saved (it's a tabbed UI). I don't want to silently save any other pending changes, which would happen if all the editing UI elements share a common EntityContext.

The approach I'm taking is to assign separate EntityContexts to each of the editing elements. This seems to work, with all the contexts being updated to reflect new records when one is created (e.g., creating a new name record in one DataGrid has that name showing up "immediately" when the user is given the option of picking a name on another DataGrid tied to a different EntityContext).

Is this a reasonable approach? What are the downsides to it? Is their another pattern I should be following?

- Mark

Dec 29, 2010 at 5:15 PM

Firstly you could use the IsUnique property to make a field unique in a database table. However that will not help you since you really want a rule based on first/last name combination. I would hit the database and check for the existing name instead of saving after each record. In other words instead of saving to make sure you have a unique name just have a check function and prompt the user if there is an error. That was you do not need to commit anything to the database until you really want to (on Form close I assume). Did I understand your question correctly?