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 https://stackoverflow.com/a/41257827/2630817 — Just code, Nov 20 '18 at 08: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, Nov 20 '18 at 09: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, Nov 20 '18 at 09:22

Salah Akbari · Accepted Answer · 2018-11-20T08:54:52.060

6

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.

edited Nov 20 '18 at 08:54

answered Nov 20 '18 at 08:27

Salah Akbari

39,330
10
79
109

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 Nov 20 '18 at 09:00
@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 https://stackoverflow.com/a/2864174/2946329 – Salah Akbari Nov 20 '18 at 10:10
1

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 :) – xcelm Nov 20 '18 at 13:28
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 Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet? – Salah Akbari Nov 20 '18 at 15:22
fluent API solution doesn't seem to be working. I've set id column to [System.ComponentModel.DataAnnotations.Schema.DatabaseGenerated(DatabaseGeneratedOption.Identity)] otherwise i'm getting exception. After that I'm getting "OptimisticConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions." on any value that I'm trying to fit in – xcelm Nov 20 '18 at 15:24
I just tried using Fluent API solution and also dropped the ID column. Now it is working exactly how I wanted, but I need the ID column. Now I know I am difficult customer :) but I kinda need to combine first and second proposed solution – xcelm Nov 20 '18 at 16:50
Thanks a lot! Accepted – xcelm Nov 20 '18 at 20:58

score 4 · Answer 2 · answered Nov 20 '18 at 08:27

4

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.

answered Nov 20 '18 at 08:27

Harambe

997
2
8
17

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? – xcelm Nov 20 '18 at 15:34

Unique primary key using Entity Framework

2 Answers2