5

First, I am new to MVC.

I have a Project table which has ProjectID, ProjectNumber, and ProjectDescription fields. The ProjectId is an entityKey of type int, the ProjectNumber needs to be a unique constraint.

How do I do this in entity framework 6.1.3?

in my Project class I have 

        public int ProjectID { get; set; }
        [Index(IsUnique = true)]
        [StringLength(200)]
        public string ProjectNumber { get; set; }
        public string ProjectDescription { get; set; }

when I generate database from model, the field is not set as unique in the database.

what am I doing wrong?

Sampath
  • 63,341
  • 64
  • 307
  • 441
user3726459
  • 172
  • 1
  • 14
  • Just to make sure, you have a reference to this namespace: `using System.ComponentModel.DataAnnotations.Schema;` right? Also try adding the maxlength attribute just to see if it makes a difference: `[MaxLength(200)]` – Keyur PATEL Sep 14 '16 at 01:49
  • yes, I have the reference to the name space System.ComponentModel.DataAnnotations.Schema . I made the change to `[Index(IsUnique = true)] [MaxLength(200)] public string ProjectNumber { get; set; } ` after I made the change, I saved the class, then do I go the model designer and generate database from model? – user3726459 Sep 14 '16 at 02:18
  • Yes, try `[MaxLength(200)]` instead of `[StringLength(200)]`, or if that doesn't work try both of them. The server needs to know there is a limit to the number of characters before adding a unique constraint. – Keyur PATEL Sep 14 '16 at 02:21
  • I guess maybe I am misunderstanding something. By adding this '[MaxLength(200)] [Index(IsUnique = true)] ', should the generated script which creates the database tables have an add constraint clause? – user3726459 Sep 14 '16 at 02:29
  • It is automatic, if you're generating database from model. You don't need to worry about the script and its constraints if you set your model correctly. For more reference on difference between StringLength and MaxLength: [StringLength vs MaxLength attributes](http://stackoverflow.com/questions/5717033/stringlength-vs-maxlength-attributes-asp-net-mvc-with-entity-framework-ef-code-f) – Keyur PATEL Sep 14 '16 at 02:34
  • the model is just one table. Project ID is the entity key and Project Number needs to be unique. With these settings, I am able to add a duplicate project number – user3726459 Sep 14 '16 at 02:36
  • I tried it, but I am still able to insert duplicates. – user3726459 Sep 14 '16 at 02:43
  • I doubt if there is a way to Add Unique Key using EF designer. You have to be either using Code First or Database First to be able to do this. One thing that you could try is editing the edmx manually but I'm not sure about that. – sachin Sep 14 '16 at 12:57

1 Answers1

4

Based on the answer in Entity Framework code first unique column

Try this code:

public int ProjectID { get; set; }
[Index("ProjectNumber_Index", IsUnique = true)]
[MaxLength(200)]
public string ProjectNumber { get; set; }
public string ProjectDescription { get; set; }

And make sure the string is not set to nvarchar(MAX) in your SQL Server or you will see an error with Entity Framework Code First.

Community
  • 1
  • 1
Keyur PATEL
  • 2,299
  • 1
  • 15
  • 41
  • it is not working for me. All fields are nvarchar(50). The entitykey is an int. I am not sure what I am missing or what I am exactly doing wrong. What is supposed to happen when I insert a record with a ProjectNumber that already exists? – user3726459 Sep 14 '16 at 02:56
  • It is supposed to give you a DbUpdateException with the following message: `Cannot insert duplicate key row in object 'db.Project' with unique index 'Index'. The duplicate key value is (...). The statement has been terminated.` – Keyur PATEL Sep 14 '16 at 03:03
  • If my answer still doesn't help you, I'm afraid you will have to wait for someone more experienced than me to answer it. I apologize for that. – Keyur PATEL Sep 14 '16 at 03:05
  • @user3726459 did you create a db (or data migration) after the model change ? – Sampath Sep 14 '16 at 05:03
  • @Sampath when I create the db by going to the projectmodel.edmx, right click in the white space somewhere next to the entities and select generate database from model. The script gets generated, I run it. The database is created. The project.cs under ProjectModel.tt gets overwritten after I run the database script and then build solution, and once it gets overwritten this code `[Index("ProjectNumber_Index", IsUnique = true)] [MaxLength(200)]` which i added is gone. – user3726459 Sep 14 '16 at 07:11
  • @Sampath an additional note, when I created my model, I selected Empty EF Designer Model. – user3726459 Sep 14 '16 at 07:16
  • @user3726459 is this database first or what ? – Sampath Sep 14 '16 at 07:22
  • @Sampath that is correct. Sorry, I am new to EF and I didn't realize there were different ways to build a model with EF. it looks like the answer above is for Code First. – user3726459 Sep 14 '16 at 07:43