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

David Thielen 2,231 Reputation points
2023-03-10T00:03:02.3+00:00

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

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
694 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,601 questions
{count} votes

Accepted answer
  1. Jack J Jun 24,276 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.

    User's image

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Viorel 111.5K 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; }
    }
    

  2. Bruce (SqlWork.com) 54,621 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