Is there a way to say an entry is unique across 2 column values?

David Thielen 2,256

My question is in the context of Entity Frameworks talking to SQL Database.

I have a class as follows:

   public class County
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StateId { get; set; }
        public State State { get; set; }
    }

I have one of these records for each county in each state. The Name is not unique because there is an Orange County in both California and Florida. However, the combination of Name + StateId is unique.

Is there a way to specify this in Entity Framework so that it tells SQL Database to enforce that uniqueness?

thanks - dave

Karen Payne MVP 35,031

Untested

class SomeContext : DbContext
{
    public DbSet<County> County { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<County>()
            .HasIndex(c => new { c.Name, c.StateId })
            .IsUnique(true);
    }
}

David Thielen 2,256 Reputation points

2023-03-15T01:06:42.6833333+00:00

thank you. I selected Jack's answer (same as your comment) because I can't select a comment as the answer. (And he was first.)

Accepted answer

Jack J Jun 24,286 Reputation points Microsoft Vendor

2023-03-13T09:12:27.0966667+00:00
@David Thielen, you could try the following two methods to use fluent api to make a column pair unique.

First of all, we need to make some changes in my class:

Then, we could use the following two methods to do it.

public class County { public int Id { get; set; } [Key] [Column(Order = 0)] public string? Name { get; set; } [Key] [Column(Order = 1)] public int StateId { get; set; } } protected override void OnModelCreating(ModelBuilder modelBuilder) { //modelBuilder.Entity<County>().HasKey(x => new { x.Name, x.StateId }); //First method modelBuilder.Entity<County>().HasIndex(x => new { x.Name, x.StateId }).IsUnique(); // Second method }

Also, to verify, Id will remain the primary key - correct?

No, based on my test, Id is not the primary key.

Best Regards,

Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

1 person found this answer helpful.
Jack J Jun 24,286 Reputation points Microsoft Vendor

2023-03-14T09:03:58.3533333+00:00

@David Thielen, have you considered my solution to use fluent api?

David Thielen 2,256 Reputation points

2023-03-14T15:02:46.6166667+00:00

Yes - this is it! thanks

David Thielen 2,256 Reputation points

2023-03-15T01:05:49.3833333+00:00

I suggest you remove the [Key] attributes from your answer as they are redundant to the fluent code.
Sign in to comment

2 additional answers

Viorel 112.1K Reputation points

2023-03-10T06:35:15.4233333+00:00
According to https://learn.microsoft.com/en-us/ef/core/modeling/keys:

[PrimaryKey(nameof(Id), nameof(Name))] public class County { public int Id { get; set; } public string Name { get; set; } public int StateId { get; set; } public State State { get; set; } }
Please sign in to rate this answer.
David Thielen 2,256 Reputation points

2023-03-10T17:35:33.8666667+00:00

First off, thank you. This may be the solution. But I have a follow-on question...

What does making the two using PrimaryKey mean? Reading up on it, clearly that will force the combo to be unique as it's the unique identifier of that row. But I can't find anything else that says what else it implies.

And will the Id still be unique by itself and auto-increment? Because I want to be able to get a record by just its Id.

thanks - dave

Viorel 112.1K Reputation points

2023-03-10T22:15:36.4066667+00:00

Regarding the last question, I think that you can add an attribute:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; }

David Thielen 2,256 Reputation points

2023-03-11T01:35:48.2066667+00:00

I think that is already set for the Id as EF will select it as the PK per https://stackoverflow.com/questions/49799970/adding-unique-identifier-with-fluent-api

My worry is how does it change things for EF making the combined keys a PK. I'm guessing it will work but my concern is - is this the correct way to approach this. And will it have any negative side effects.

I've had too many cases in the past where I got something to work and then after an update of a library, it breaks. Because I did something the wrong way and it was an unknown side effect that I was making use of.

thanks - dave

David Thielen 2,256 Reputation points

2023-03-11T01:46:22.6466667+00:00

I've just been reading a number of articles and I don't think a composite key is the solution to this issue. The purpose of a composite key is to combine them into a unique key and index off that combined key.

That's not what I need. What I need is two things:

Use Id as the primary key. The Id will be unique across all rows in the database and can be used to query and get that row. This is critical because the Name value can be changed but the Id is immutable.

The Id + Name is guaranteed to be unique. There is no PK usage for this combination, it's just a uniqueness constraint.

thanks - dave

David Thielen 2,256 Reputation points

2023-03-11T19:59:57.16+00:00

@Viorel Can you please update your answer to show that solution using fluent and then I'll accept it as the answer. I think it's HasIndex().IsUnique() - but I'm not sure if that's exactly it.

Also, to verify, Id will remain the primary key - correct?

thanks - dave

David Thielen 2,256 Reputation points

2023-03-13T17:36:06.3066667+00:00

@Viorel Can't this can be done with a composite Index (as opposed to composite Primary Key) with the index set to unique?

If so, if you write that as an answer, using fluent instead of attributes, I'm happy to accept that as the answer.

thanks - dave
Sign in to comment
Bruce (SqlWork.com) 55,366 Reputation points

2023-03-11T15:59:54.3633333+00:00

You want to define a unique constraint. This is done with the Index annotation.

https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations
Please sign in to rate this answer.
David Thielen 2,256 Reputation points

2023-03-11T18:51:14.9733333+00:00

Bruce - unless I am missing something that is discussing how to make a single column unique. I need to make a column pair unique. There is an Orange County California and an Orange County Florida. But there is only one Orange County in each state.
Sign in to comment