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?

Answer 1

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.

Answer 2

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.