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

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, 2018 at 8:54

answered Nov 20, 2018 at 8:27

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

xcelm Over a year ago

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; }

Salah Akbari Over a year ago

@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

xcelm Over a year ago

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 Over a year ago

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?

Salah Akbari Over a year ago

@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

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, 2018 at 8:27

Harambe

9972 gold badges8 silver badges17 bronze badges

1 Comment

xcelm Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related