CODEWITHSUNDEEP
c#entity-framework-6data-annotationsentity-relationshipunique-constraint
Donald N. Mafa
Donald N. Mafa

Reputation: 5283

Entity Framework: Indexing a child entity based on a value on the parent entity for uniqueness

I have a quick question and looking for the best way to do this, whether EF has the capability or not, am not sure? I am using EntityFramework 6.3.

I have the following parent-child scenario, 

public class Application{
 [Key]
 public int ApplicationId {get;set;}
 public string Name {get;set;}
 public string Status {get;set;}

 public virtual List<Document> Documents {get;set;}
}

public class Document{
 [Key]
 public int DocumentId {get;set;}
 [Index("IX_ApplicationDocument", 1, IsUnique = true)]
 public string DocumentType {get;set;}
 [Index("IX_ApplicationDocument", 1, IsUnique = true)]
 public string Name {get;set;}

 public int ApplicationId {get;set;}

 [ForeignKey("ApplicationId")]
 public Application Application {get;set;}
}

So an application is made to a department, and stored in the database, each application has a status and when submitted, status of pending, because various validation has to occur before it is approved. When an application is rejected, the submitter has to make a new application (please note I used a minimalistic example than what it actually is), however, the applicant may submit the same documents again. The problem is, this already exist in the system and can not be duplicated. As you can see, the second time they attempt to submit it will throw a constraint exception. How can I overcome this using EF, is there a way to create a constraint based on the parent's status, or is this something that can only be done programmatically?

Upvotes: 0

Views: 697

Answers (2)

Donald N. Mafa
Donald N. Mafa

Reputation: 5283

So unfortunately I have searched around and there is no solution for this for EF 6. The best way I can do this was following the guidance of the following article, where you manually add the Filtered Index in your migration after table creation.

Blog

Upvotes: 0

Syed Wahhab
Syed Wahhab

Reputation: 153

Dont know if its helps you in your case or not, but check this out

Assuming your entity is defined as

public class Entity
{
    public int Id { get; set; }
    public int Parent { get; set; }
    public int Child { get; set; }
}

Following fluent API code will create index as you desire:

modelBuilder.Entity<Entity>().HasIndex(p => new {p.Parent, p.Child})
        .HasFilter("isdeleted = 0")
        .HasName("unq_t_parent_child");

SQL generated

CREATE INDEX [unq_t_parent_child] ON [Entity] ([Parent], [Child]) WHERE isdeleted = 0;

HasIndex defines index over properties in the table Entity

HasFilter allows you to set a filter for your index. This value is sql so you need to make sure you are writing correct sql syntax.

HasName configures the name of the index.

(If you map Entity to table t & the properties to their column names, migrations will create exactly same sql as you want.)

Also a check constraint is different from unique index. If you are looking to add check constraint then you need to use migrationBuilder.Sql in your migration file.

Upvotes: 1

Related Questions