How to set unique constraint for nullable column with entity framework?

Question

I am using entity framework version 6 and i have a model like this:

public class SizeCount
    {
        public int Count { get; set; }
        public Size Size { get; set; }
        public long? SizeId { get; set; }
        public Color Color { get; set; }
        public long? ColorId { get; set; }
        public Product Product { get; set; }
        public long ProductId { get; set; }
    }

I want to prevent from ColorId and SizeId both be null and i want to ProductId,ColorId,SizeId be unique. some example record:

ProductId SizeId ColorId
1         null   1       > allow
1         null   1       > not allow
1         1      null    > not allow
2         1      null    > allow
2         null   1       > not allow

SizeId or ColorId can be null. Is there any attribute that can help me in the entity framework or i should check it manually?

Answer 1

Unique constraint with nullable column

In case anyone else is trying to do something similar with fluent API db contexts, this worked for me...

modelBuilder.Entity<AppointmentRequest>()
    .HasIndex(r => new { r.CustomerId, r.StartAt })
    .HasFilter("StartAt IS NOT NULL")
    .IsUnique();

modelBuilder.Entity<AppointmentRequest>()
    .HasIndex(r => r.CustomerId)
    .HasFilter("StartAt IS NULL")
    .IsUnique();

It effectively treats null like a value.

Answer 2

Hope this helps, its a slightly modified version. for the SizeCount class -

using System.ComponentModel.DataAnnotations.Schema;

public class SizeCount
{
    public int Id { get; set; }
    public int Count { get; set; }

    [Index("IX_SizeUnique", 1, IsUnique = true)]
    public int SizeId { get; set; }
    public virtual Size Size { get; set; }

     [Index("IX_ColorUnique", 1, IsUnique = true)]
    public int ColorId { get; set; }
    public virtual Color Color { get; set; }

     [Index("IX_ProductUnique", 1, IsUnique = true)]
    public int ProductId { get; set; }
    public virtual Product Product { get; set; }

}

public class Product
    {
        public int Id { get; set; }
    }

    public class Color
    {
        public int Id { get; set; }
    }

    public class Size
    {
        public int Id { get; set; }
    }

The Context

public class TestDbContext: DbContext
    {
        public DbSet<SizeCount> SizeCounts { get; set; }
        public DbSet<Product> Products { get; set; }
        public DbSet<Size> Sizes { get; set; }
        public DbSet<Color> Colors { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
           base.OnModelCreating(modelBuilder);
        }
    }

I have not fully tested the code, but with some jiggery pokery you should be able to get there. I however tested it with migrations an the table generated will look like:

CreateTable(
                "dbo.SizeCounts",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Count = c.Int(nullable: false),
                        SizeId = c.Int(nullable: false),
                        ColorId = c.Int(nullable: false),
                        ProductId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Colors", t => t.ColorId, cascadeDelete: true)
                .ForeignKey("dbo.Products", t => t.ProductId, cascadeDelete: true)
                .ForeignKey("dbo.Sizes", t => t.SizeId, cascadeDelete: true)
                .Index(t => t.SizeId, unique: true, name: "IX_SizeUnique")
                .Index(t => t.ColorId, unique: true, name: "IX_ColorUnique")
                .Index(t => t.ProductId, unique: true, name: "IX_ProductUnique");