Entity Framework 4 - Username and Email columns unique problem

Question

I have a User entity in my entity model:

Username and Email should be unique but for now EF4 doesn't support this.

So I wrote this code to ensure uniqueness :

public void CreateNewUser(string i_UserName, string i_Email)
{
    using (ModelContainer context = new ModelContainer())
    {
        User usr;

        usr = context.UserSet.Where(u => u.Username == i_UserName).SingleOrDefault();
        if (usr != null)
            throw new Exception("Username not unique");

        usr = context.UserSet.Where(u => u.Email == i_Email).SingleOrDefault();
        if (usr != null)
            throw new Exception("Email not unique");

        context.UserSet.AddObject(new User() { Username = i_UserName, Email = i_Email });
        context.SaveChanges();                               
    }
}

If this is right approach, do I have way to automatically preform this code whenever context.UserSet.AddObject() is called? Or a more elegant way does exist?

This question may help: http://stackoverflow.com/questions/3006179/unique-value-in-table-using-ef4-and-locking — Stephen Chung, Apr 02 '11 at 13:39
@Bugai13 If Username and Email are the primary key I can still have two rows that have identical Usernames. — Yaron Levi, Apr 02 '11 at 13:48
I'm not sure that throwing an exception is the best way to do this, unless it's truly an exceptional circumstance. — Zack Marrapese, Apr 25 '12 at 11:28

neontapir · Answer 1 · 2011-10-26T16:41:22.970

5

I don't know of a more elegant way. Rather than using SingleOrDefault, I think you want something like:

bool isUniqueUserName = !context.UserSet.Any(u => u.Username == i_UserName);

for performance. Any() stops at the first match and doesn't have to enumerate the entire sequence.

edited Oct 26 '11 at 16:41

answered Apr 02 '11 at 13:40

neontapir

4,698
3
37
52

As Ladislav Mrnka said, I will have concurrency problems. I haven't though of that, and it will be a major problem. I prefer to define unique constraint manually in the DB, even if I have to do it each time after I am generating the DB form the entity model – Yaron Levi Apr 02 '11 at 15:39
It's true that a database constraint will solve this. I believe you can solve it in EF, though. This question is an example of how. – neontapir Apr 02 '11 at 16:05

score 2 · Accepted Answer · answered Apr 02 '11 at 14:16

Correct way is defining Unique index in database and catching an exception. These checks in code must be much more complex then simple: say me if the user already exists. First of all this is not only problem of insert but user can also usually modify his email. Another problem is concurrency - in very corner case you can have two users inserting same records in the same time. In both threads test queries can return that user name and email are not registered but during the saving one thread will get an exception (if you have unique index in db) or duplicit record will be created. If you want to avoid it you must lock your database records and lock table for insertion (serializable transaction). Such operation can decrease throughput of your application.

Another fact is that you will do 2 additional queries before each insert and at least one query before each update.

But defining Unique constraint in the db, and not reflecting it in the entity model won't cause problems ? — Yaron Levi, Apr 02 '11 at 14:30
If you don't expect to build relations on top of unique keys and if you don't recreate databse from model then it will not cause any problems. — Ladislav Mrnka, Apr 02 '11 at 14:36

score 0 · Answer 3 · answered Apr 02 '11 at 13:40

0

I use the same approach. Just ensure that you have one method that insert it and you should be find. Similar to factory method pattern.

answered Apr 02 '11 at 13:40

ysrb

6,693
2
29
30

BrandonZeider · Answer 4 · 2011-04-02T14:53:46.507

Branching off of @ysrb's answer (having a single method that does the User insert), a more foolproof solution might be to hook into the ObjectContext.SavingChanges event and do your test there if a User entity is being saved. This way you can be sure your logic always fires.

Example:

        IEnumerable<ObjectStateEntry> changes = this.ObjectStateManager.GetObjectStateEntries(EntityState.Added);
        foreach (ObjectStateEntry stateEntryEntity in changes)
        {
            if (!stateEntryEntity.IsRelationship && stateEntryEntity.Entity != null)
            {
                if (stateEntryEntity.Entity is User)
                {
                    //Do you work here
                }
            }
        }

But inside the SavingChanges event, how can I Know which entity is being saved and what are the values ? — Yaron Levi, Apr 02 '11 at 14:15

Entity Framework 4 - Username and Email columns unique problem

4 Answers4