Entity Framework 6 Code First approach - unique co

Question

I am using EF 6 with code first approach. In addition to database constraints check at database level, I would like to provide unique constraints check at POCO level. I have followed article Setting unique Constraint with fluent API? where someone suggested to use [Index()] attribute. I have applied the same in my poco class but looks like it is still throwing an exception from Database level.

Here is my code:

[Key]
public decimal OrderId { get; set; }

[Index("ORDER_CC", 2, IsUnique = true)]
public decimal? MemberId { get; set; }

[Index("ORDER_CC", 3, IsUnique = true)]
public decimal? ItemId { get; set; }

[Index("ORDER_CC", 1, IsUnique = true)]
public decimal? OrderNumber { get; set; }

public decimal? Cost { get; set; }

public DateTime Time { get; set; }

I am not sure what I am doing wrong here?

I also want to ask followings:

1. Do I need to keep the same index name as the one I have on my database table for Order?
2. How do I know, it is getting validate against EF constraints check or Database constraints check?
3. I have kept the same order for index on POCO class compare to one defined in Order table in database.

Here is my Database script:

CREATE UNIQUE INDEX xyz.ORDER_CC ON xyz.Order
(OrderNumber, MemberId, ItemId)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

Any idea?

Answer 1

The problem is not in the Index attribute, it's in your expectations of what it does.

The Index attribute doesn't validate a unique constraint, it only instructs Entity Framework code-first to create an index in the database during a migration. From your questions it is clear that you use EF code-first, i.e. POCO's and coded mappings, but you work with a preexisting database. In this case, applying index attributes is useless.

So that also answers your question where the constraint is checked: in the database. And that's why it's still throwing errors. If you want a unique constraint in code you'll have to write it yourself.

Answer 2

Update

I have posted about this at code project

In general, it depends on the attributes that you put on the classes to generate your unique indexes

---

Thanks to Shimmy in this answer https://stackoverflow.com/a/15999805/105445

You can do it yourself by creating a custom ValidationAttribute to validate the property in a generic way. Then you can use it like this:

[ShouldBeUnique()]
public string SomeProperty { get; set; }

Take a look at this example

http://blogs.microsoft.co.il/shimmy/2012/01/23/uniqueattribute-that-validates-a-unique-field-against-its-fellow-rows-in-the-database-inherits-dataannotationsvalidationattribute/