CODEWITHSUNDEEP
c#ef-code-firstentity-framework-6
Umair Anwaar
Umair Anwaar

Reputation: 1136

Composite key one to second and one to third Entity Framework 6 code-first

I have a scenario to create key combination. Is there any way to do it with EF 6 code-first?

I have to add restriction with device and employee to don't be duplicated in the same branch. branchId is a foreign key.

I try to use below class but its allowing me to add branch = 1,device =2 and employee = 1.

public class Employee
{
    public Employee()
    {
        Departments = new HashSet<Department>();
    }

    [Key]
    public long EmployeeId { get; set; }

    [ForeignKey("Branch")]
    [Index("BranchEmpDev", IsUnique = true, Order = 1)]
    public long BranchId { get; set; }

    [Index("BranchEmpDev", IsUnique = true, Order = 2)]
    public string DevRegNumber { get; set; }

    [Index("BranchEmpDev", IsUnique = true, Order = 3)]
    public string EmployeeNumber { get; set; }

    public virtual Branch Branch { get; set; }
    public virtual ICollection<Department>  Departments { get; set; }


}

below image is showing what I need.

enter image description here

Upvotes: 1

Views: 113

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205729

The way I understand it, currently you have a single unique index (constraint) called "BranchEmpDev" on (BranchId, DevRegNumber, EmployeeNumber) field combination, but you want instead two unique indexes - one on (BranchId, DevRegNumber) field combination (let call it "BranchDev") and another on (BranchId, EmployeeNumber) field combination (let call it "BranchEmp").

It's possible, although not so intuitive (composite keys/indexes are not intuitive in EF6 in general). To do that with data annotations, you would use IndexAttributes as in your current model, but with different name and apply them on the corresponding properties with the desired order. Which means that BranchId will have two IndexAttributes - one for each index it is part of.

The relevant part of the solution looks like this:

[ForeignKey("Branch")]
[Index("BranchEmp", IsUnique = true, Order = 1)]
[Index("BranchDev", IsUnique = true, Order = 1)]
public long BranchId { get; set; }

[Index("BranchEmp", IsUnique = true, Order = 2)]
[StringLength(10)]
public string DevRegNumber { get; set; }

[Index("BranchDev", IsUnique = true, Order = 2)]
[StringLength(10)]
public string EmployeeNumber { get; set; }

which generates the following part in the table creation migration (just for verification):

.Index(t => new { t.BranchId, t.EmployeeNumber }, unique: true, name: "BranchDev")
.Index(t => new { t.BranchId, t.DevRegNumber }, unique: true, name: "BranchEmp");

As you can see, EF consolidates the IndexAttributes with the same name and uses the Order to build the composite column list.

Upvotes: 2

Related Questions