Add discriminator filter for unique index on derived type

[eduherminio]

Issue description

Providing TPH & the following, simplified model:

    public class Organization
    {
        public string Name { get; set; }
    }
    public class Company : Organization { }
    public class Department : Organization { }

OnModelCreating: the following restriction is applied to all Organizations, so two Departments with the same name cannot be created.

    modelBuilder.Entity<Company>()
        .HasBaseType<Organization>();

    modelBuilder.Entity<Company>()
        .HasIndex(c => c.Name).IsUnique();

Workaround

We've found the following workaround, using a custom filter:

    modelBuilder.Entity<Organization>()
        .HasDiscriminator<int>("Discriminator")
        .HasValue<Company>(1)
        .HasValue<Department>(2);

    modelBuilder.Entity<Company>()
        .HasIndex(c => c.Name).IsUnique()
        .HasFilter("Discriminator IN (1)");

However, a more direct way of configuring it would be desirable.

Further technical details

EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[ajcvickers]

@eduherminio Can you explain a bit more what the goal is here? If TPH is setup and a query is made for Company entities, then EF will only return Company entities, so it's not clear to me what the filter is for. I'm sure I must be missing something about what you are doing.

Also, note that if you want the discriminator can be a normal mapped property of your entity type and can be accessed as such. By default, it is created as a shadow property, which can still be accessed in queries using the static EF.Property method.

[eduherminio]

Using this restriction I want Company (and only Company) names to be unique.

    modelBuilder.Entity<Company>()
        .HasIndex(c => c.Name).IsUnique();

Due to TPH, and since the field Name belongs to Organization, that restriction applies to every Organization (parent of both Company and Department), so there can't be two Departments (or any other class inherited from Organization) with the same name.

As far as I can understand, this three restrictions would have the same effect nowadays:

    modelBuilder.Entity<Company>()
        .HasIndex(c => c.Name).IsUnique();

    modelBuilder.Entity<Department>()
        .HasIndex(c => c.Name).IsUnique();

    modelBuilder.Entity<Organization>()
        .HasIndex(c => c.Name).IsUnique();

If I apply an index to Company (children) and not Organization (parent), I expect that uniqueness condition to affect only to Companies (although the field, Name in the example, belongs to the parent entity).

I assume changing that behavior may not be easy (or even desirable), so the initial request was a simpler way to restrict constraints to children entities when applied to a parent's field. In other words, adding a condition to .HasIndex() based on the discriminator.

The filter can be used as a workaround to limit the restriction to some types of Organizations (just Companies, in the example), but it doesn't seem a straightforward way of configuring a restriction.

[ajcvickers]

@eduherminio Thanks for the additional info. We'll discuss this and get back.

[MCFHTAGENTS]

I thought I would add to this. I have a field in the database that needs to be a discriminator; part of the primary key of the table, and a text field of defined length and/or an integer constrained as a foreign key. I don't believe I can do this with the current fluent API.

I have worked round this by adding a second column that is the key and a text field

[ajcvickers]

@MCFHTAGENTS This seems like a different scenario. Can you please file a new issue with a runnable project/solution or complete code listing that demonstrates the issue?

[agrawalsaurabh21]

#Devart , #Oracle

modelBuilder.Entity()
.HasDiscriminator("Discriminator")
.HasValue(1)
.HasValue(2);

modelBuilder.Entity()
.HasIndex(c => c.Name).IsUnique()
.HasFilter("Discriminator IN (1)");

This workaround is working fine with Sql Server but not with Oracle. I am using Devart.Data.Oracle.EfCore (9.11.980) with dotnet core 3.1 framework. Can u tell me soln. for Oracle with core 3.1 .

[roji]

@agrawalsaurabh21 it seems that Oracle does not support filtered indexes, but at the article shows, you can work around this with a function-based index that returns null. EF Core won't generate the DDL for creating this index, but you can use raw SQL in your migration to create it.

[agrawalsaurabh21]

@roji Below is the fix for the Oracle , we need to create a custom column in base table which will act as Discriminator and by doing so we can add this custom column to HasIndex method.

Code :

public string TableName { get; set; }

modelBuilder.Entity().HasDiscriminator("TableName").HasValue("ChildTable1").HasValue("ChildTable2")
.HasValue("ChildTable3");

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