3

After much research it seems Entity Framework 4.4 doesn't support Unique constraints. Yes it can & should be done at the database, but I'd much prefer it happen in model validation so the warning to user is prettier.

It would be ideal for programmers to be able to decorate the property with a [Unique] attribute and it should be possible somehow, eg.:

public class UserGroup
{

    public int UserGroupID { get; set; }

    [Required]
    [Unique]
    public string Name { get; set; }

    [Required]
    public string Description { get; set; }

}

Options I'm considering:

1) Have the repository do some extra work at SaveChanges(), scan for [Unique] attributes on modified entities and hit the database to check uniqueness. Downside: this validation only happens when we call SaveChanges(), ideally it can happen earlier (eg. when the UI control validates).

2) Give the UserGroup model a lazy-loaded navigation property to AllUserGroups:

public virtual ICollection<UserGroup> AllUserGroups { get; set; }

Then program UniqueAttribute{} to scan this property and check the values etc.

QUESTION: how can I configure Entity Framework (code first) to load all records into this "navigation property"? It only seems to want a navigation property with foreign keys etc while I just want them all.

3) Manually code this validation in the UI - terrible & absolute last resort.

QUESTION: are there any better options for enforcing a Unique Constraint via validation at the model level?

Regards, -Brendan

Brendan Hill
  • 3,406
  • 4
  • 32
  • 61
  • I think this is a good question but as you have rightly pointed out EF actually doesnt know if its local representation of a navigation property is complete so it cant pre-validate unique constraints. Have you concidered catching and parsing the SQL exception and producing a nicer error for the user that way instead as I feel this would be far easier than what you are proposing (which essentially requires caching the DB) – undefined Jan 16 '13 at 23:13
  • Hi Luke, I'll probably go with (1) (funky work in repository) if no-one can advise how to do (2). Yes (2) must hit the database and load the entire table which sucks. Alternatively, if DbSet<> could be exposed here then it could simply query directly. Ahh... our team is regretting going down the POCO class - EF seems to struggle with anything beyond the most simple database requirements. – Brendan Hill Jan 16 '13 at 23:23
  • I suppose what I'm saying is that both of those options seem pretty complex and not terribly performant. I dont think either are required. SQL will throw a pretty descriptive error (on ef's SaveChanges) when you violate a unique constraint on the database. By catching this error at a high level you can parse it and work out any validation messages you want at that stage. I dont think that pre-validation is really the path you want to go down, and it will be an issue with any of the ORMs I know of (and with raw SQL too for that matter) so i dont think this is a failing of CodeFirst – undefined Jan 16 '13 at 23:40
  • Maybe it's something lacking in ORM's in general - but validation should be done on the model, and checking unique name is a validation task. The fact that the surrounding infrastructure struggles to support this (whatever ORM) seems a limitation to me. It also gives rise to an inconsistent user experience. So I'm still searching! – Brendan Hill Jan 16 '13 at 23:52
  • What do you mean inconsistent user experience? The problem is that unique constraint checking happens at the database level not on the application side of the query. This means that at best you can have a whole copy of the data locally and prevalidate the query, however it will still get rechecked at the database, and may still fail if your origional cache is out of date. – undefined Jan 17 '13 at 00:05
  • My feeling is that if you still want to do this you are working against the tools not with them, *Your going to have a bad time* – undefined Jan 17 '13 at 00:06
  • Yes - I'm working against the tool & having a bad time :( All in the interest of the users though. It's an inconsistent user experience to have some validation styling apply as they type in/interact with the screen, but other rules apply when they click Save. – Brendan Hill Jan 17 '13 at 00:10
  • Ahh that makes a little more sense that you want this for instant feedback, have you concidered running your edit inside a rollback statement inside a validation attribute, this should be near instant and will guarantee you that at the point of the rollback the constraint was valid. – undefined Jan 17 '13 at 00:19
  • Well, in the end I went with (3) and coded it into the UI. Terrible solution but seems the only way to do what we want, and oh well... only 4 lines of code. Thanks for the feedback though. – Brendan Hill Jan 17 '13 at 04:08

1 Answers1

3

I found this, seems to be the one you're looking for:

UniqueAttribute that validates a unique field against its fellow rows in the database (inherits DataAnnotations.ValidationAttribute)

Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632