Unique primary key using Entity Framework

Question

I'm new to EF. Let's say I have a table in DB like this:

ID FirstName LastName DateOfBirth ------------------------------------------- 1 John Smith 1.1.1990 2 Mary Wilson 5.1.1991

Now I insert new entity into the table using EF:

dbcontext.Persons.Add(new Person { FirstName = "John", LastName = "Smith", DateOfBith = "1.1.1990" }); dbcontext.SaveChanges();

I need the code to throw an exception because this row already exists in the database but what EF does is that it increments the ID column by 1 and creates new record:

ID FirstName LastName DateOfBirth -------------------------------------------- 1 John Smith 1.1.1990 2 Mary Wilson 5.1.1991 3 John Smith 1.1.1990

Is EF even capable of doing this?

Apply unique constraint stackoverflow.com/a/41257827/2630817 — Just code
– Just code, Commented Nov 20, 2018 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model class Person in C# ? — marc_s
– marc_s, Commented Nov 20, 2018 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime — xcelm
– xcelm, Commented Nov 20, 2018 at 9:22

Salah Akbari · Accepted Answer · 2018-11-20 08:54:52Z

You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:

[Key] public string FirstName { get; set; }

Or by using a unique constraint:

[Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime DateOfBirth { get; set; }

You can also use fluent API for this purpose:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth }); }

Or if you are using the DB-first approach, you can declare it in your database.

thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?

Harambe · Accepted Answer · 2018-11-20 08:27:15Z

With EF you would need to do something like this:

[Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime DateOfBirth { get; set; }

This will put a unique constraint across the 3 columns.

Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?

Collectives™ on Stack Overflow

Unique primary key using Entity Framework

2 Answers 2

8 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Linked

Related