Add discriminator column as a part of a unique index in Entity Framework

c# entity-framework entity-framework-6 sql-server

Question

Is it possible to set up a Discriminator column and a second string column as a unique index constraint using the EF Fluent API?

I have a list of IDs that can be any of a variety of sorts. The identifying string is stored in a property of type string that is a part of every identifier.

In my scenario, a client may have many identities. However, each discriminator can only have one identification with a unique string.

A identifier type is defined by an abstract class.

 public abstract class CustomerIdentifier : Entity<int>
 {
    public string Identifier { get; set; }
 }

CustomerIdentifier-derived concrete class

 class NationalIdNumberIdentifier : CustomerIdentifier
 {
 }

Using the solution provided here, Entity Framework's unique Key restrictions for multiple columns, I was able to set the index for the string column as follows.

class CustomerIdentifierMap : EntityTypeConfiguration<CustomerIdentifier>
{
    public CustomerIdentifierMap()
    {
        Property(p => p.Identifier).HasMaxLength(100).IsRequired().HasUniqueIndexAnnotation("UQ_IdentifierPerDiscriminator", 0);
    }
}

This line has to somehow specifically state that the discriminator should be subject to the unique index restriction.

1
6
5/23/2017 12:34:50 PM

Accepted Answer

Sadly, EF 6 does not support it, however EF 7 may, as indicated by "Support of Discriminator column which is part of Primary key" https://entityframework.codeplex.com/workitem/2420.

To move it up the priority list, you might want to vote on it.

1
3/24/2015 1:59:41 AM

Popular Answer

Actually, EF 6 allows you to accomplish it. Here is an illustration of how to make a unique index using the primary key.

internal class DiscriminatorServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    const string DiscriminatorColumnName = "Discriminator";
    protected override void Generate(CreateTableOperation op)
    {
        base.Generate(op);
        if (op.Columns.Any(x => x.Name == DiscriminatorColumnName))
        {
            if (op.PrimaryKey != null && op.PrimaryKey.Columns.Any())
            {
                CreateDiscriminatorIndex(op.Name, true, op.PrimaryKey.Columns.ToArray());
            }
            else
            {
                CreateDiscriminatorIndex(op.Name);
            }
        }
    }
    private void CreateDiscriminatorIndex(string tableName, bool isUnique = false, params string[] columns)
    {
        var cols = "[Discriminator]";
        if (columns.Length > 0)
        {
            cols += ", " + string.Join(", ", columns.Select(x => "[" + x + "]"));
        }
        var unique = isUnique ? "UNIQUE" : "";
        using (var writer = Writer())
        {
            var str = $@"
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Discriminator' AND object_id = OBJECT_ID(N'{tableName}'))
EXECUTE('CREATE {unique} NONCLUSTERED INDEX [IX_Discriminator] ON {tableName} ({cols})')";
            writer.WriteLine(str);
            Statement(writer);
        }
    }

}


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