Setting unique Constraint with fluent API?

c# ef-fluent-api entity-framework entity-framework-6

Question

I'm trying to build an EF Entity with Code First, and an EntityTypeConfiguration using fluent API. creating primary keys is easy but not so with a Unique Constraint. I was seeing old posts that suggested executing native SQL commands for this, but that seem to defeat the purpose. is this possible with EF6?

1
182
11/15/2015 9:18:47 PM

Accepted Answer

On EF6.2, you can use HasIndex() to add indexes for migration through fluent API.

https://github.com/aspnet/EntityFramework6/issues/274

Example

modelBuilder
    .Entity<User>()
    .HasIndex(u => u.Email)
        .IsUnique();

On EF6.1 onwards, you can use IndexAnnotation() to add indexes for migration in your fluent API.

http://msdn.microsoft.com/en-us/data/jj591617.aspx#PropertyIndex

You must add reference to:

using System.Data.Entity.Infrastructure.Annotations;

Basic Example

Here is a simple usage, adding an index on the User.FirstName property

modelBuilder 
    .Entity<User>() 
    .Property(t => t.FirstName) 
    .HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute()));

Practical Example:

Here is a more realistic example. It adds a unique index on multiple properties: User.FirstName and User.LastName, with an index name "IX_FirstNameLastName"

modelBuilder 
    .Entity<User>() 
    .Property(t => t.FirstName) 
    .IsRequired()
    .HasMaxLength(60)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(
            new IndexAttribute("IX_FirstNameLastName", 1) { IsUnique = true }));

modelBuilder 
    .Entity<User>() 
    .Property(t => t.LastName) 
    .IsRequired()
    .HasMaxLength(60)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(
            new IndexAttribute("IX_FirstNameLastName", 2) { IsUnique = true }));
270
9/27/2019 10:36:21 AM

Popular Answer

As an addition to Yorro's answer, it can also be done by using attributes.

Sample for int type unique key combination:

[Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)]
public int UniqueKeyIntPart1 { get; set; }

[Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)]
public int UniqueKeyIntPart2 { get; set; }

If the data type is string, then MaxLength attribute must be added:

[Index("IX_UniqueKeyString", IsUnique = true, Order = 1)]
[MaxLength(50)]
public string UniqueKeyStringPart1 { get; set; }

[Index("IX_UniqueKeyString", IsUnique = true, Order = 2)]
[MaxLength(50)]
public string UniqueKeyStringPart2 { get; set; }

If there is a domain/storage model separation concern, using Metadatatype attribute/class can be an option: https://msdn.microsoft.com/en-us/library/ff664465%28v=pandp.50%29.aspx?f=255&MSPPError=-2147217396


A quick console app example:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace EFIndexTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new AppDbContext())
            {
                var newUser = new User { UniqueKeyIntPart1 = 1, UniqueKeyIntPart2 = 1, UniqueKeyStringPart1 = "A", UniqueKeyStringPart2 = "A" };
                context.UserSet.Add(newUser);
                context.SaveChanges();
            }
        }
    }

    [MetadataType(typeof(UserMetadata))]
    public class User
    {
        public int Id { get; set; }
        public int UniqueKeyIntPart1 { get; set; }
        public int UniqueKeyIntPart2 { get; set; }
        public string UniqueKeyStringPart1 { get; set; }
        public string UniqueKeyStringPart2 { get; set; }
    }

    public class UserMetadata
    {
        [Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)]
        public int UniqueKeyIntPart1 { get; set; }

        [Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)]
        public int UniqueKeyIntPart2 { get; set; }

        [Index("IX_UniqueKeyString", IsUnique = true, Order = 1)]
        [MaxLength(50)]
        public string UniqueKeyStringPart1 { get; set; }

        [Index("IX_UniqueKeyString", IsUnique = true, Order = 2)]
        [MaxLength(50)]
        public string UniqueKeyStringPart2 { get; set; }
    }

    public class AppDbContext : DbContext
    {
        public virtual DbSet<User> UserSet { get; set; }
    }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow