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

Question

Is it possible using EF Fluent API to configure a Discriminator column and another string column to be part of a unique index constraint?

I have a list of identifiers, where the identifiers can be of different types. Each identifier has a property of type string which holds the identifying string.

A customer in my case can ha different identifiers. But there can only be one identifier with unique string per discriminator.

Abstract class defining a identifier type

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

Concrete class derived from CustomerIdentifier

 class NationalIdNumberIdentifier : CustomerIdentifier
 {
 }

I've managed to configure index for the string column using the answer here, Unique Key constraints for multiple columns in Entity Framework as follows

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

I need to somehow add another line here specifiying that the discrimnator should be included in the unique index constraint.

realstrategos · Accepted Answer · 2017-01-13 18:47:08Z

Actually it can be done in EF 6. Below is an example that uses the primary key to create a unique index.

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);
        }
    }

}

DannyT · Accepted Answer · 2015-03-24 01:59:41Z

1

Unfortunately it is not supported in EF 6 and may be included in EF 7 as according to "Support of Discriminator column which is part of Primary key" https://entityframework.codeplex.com/workitem/2420.

You may want to vote on it to push it up the priority list.

answered Mar 24, 2015 at 1:59

DannyT

3061 gold badge2 silver badges6 bronze badges

Comments

Saurabh Agrawal · Accepted Answer · 2020-07-09 08:50:04Z

1

I did not check with EF but verified with EF core. We need to add a custom property for Discriminator in base table and then we can use this property in HasIndex method for making unique key. I have verified it with Oracle as well as SQLServer.

Code : Custom Property for Discriminator:

public string TableName { get; set; }

Fluent API:

modelBuilder.Entity<BaseTable>().HasDiscriminator<string>("TableName").HasValue<ChildTable1>("ChildTable1").HasValue<ChildTable2>("ChildTable2").HasValue<ChildTable3>("ChildTable3");

modelBuilder.Entity<BaseTable>().HasIndex(m => new { m.Name, m.DeletedOnUtc, m.TableName }).IsUnique().HasFilter(null);

edited Jul 9, 2020 at 8:50

user10216583

answered Jul 9, 2020 at 6:31

Saurabh Agrawal

414 bronze badges

Comments

ajbeaven · Accepted Answer · 2017-02-16 01:25:22Z

0

EF 7 now has support for this using the fluent API:

modelBuilder.Entity<User>()
    .HasIndex("EmailAddress", "Discriminator")
    .HasName("UQ_EmailAddress")
    .IsUnique();

answered Feb 16, 2017 at 1:25

ajbeaven

9,60713 gold badges84 silver badges129 bronze badges

1 Comment

Ben Over a year ago

I didn't downvote, but a quick search revealed the link below on the github repo indicating that you can do this with EF 6.x fluent API: github.com/aspnet/EntityFrameworkCore/issues/4030

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related