Entity Framework Parent Child - Child refers to parent more than once

Question

I have a situation with EF5 and a complex object. The basics is that I have a parent to child complex object, but the child refers back to the parent, more than once. I have tried various options but am not finding a solution that answers the question. The closest I have got is this answer (option 2c)

My model looks like below:

public class StaffMember
{
   public virtual Guid StafId { get; set; }
   // other props

   // List of leave apps (Approved/Cancelled etc)
   public virtual ICollection<StaffLeaveApp> LeaveApps { get; set; }
}

//Staff Leave Application
public class StaffLeaveApp
{
   public virtual Guid LeaveId { get; set; }
   public virtual Guid StaffId { get; set; }
   // other props...

   // Leave approved by? (2 approvals required)
   public virtual StaffMember ApprovedBy1 { get; set; }
   public virtual StaffMember ApprovedBy2 { get; set; }
}

my mappings look like this

public class StaffMap : EntityTypeConfiguration<StaffMember>
{
    public StaffMap()
    {
        ToTable("tblStaffMembers");
        HasKey(x => x.StaffId);
        // other mappings...

        HasMany(x => x.LeaveApps);
    }
}

public class StaffLeaveAppMap: EntityTypeConfiguration<StaffLeaveApp>
{
    public StaffLeaveAppMap()
    {
        ToTable("tblStaffMembersLeaveApps");
        HasKey(x => x.LeaveId);
        Property(x => x.StaffId).HasColumnName("StaffID");

        //Child Relationships
        HasOptional(x => x.ApprovedBy1).WithMany().Map(m => m.MapKey("LeaveApprovedBy1"));
        HasOptional(x => x.ApprovedBy2).WithMany().Map(m => m.MapKey("LeaveApprovedBy2"));
    }
}

Table (sorry, no images)

  StaffID uniqueidentifier (FK - tblStaffMembers)
  LeaveID uniqueidentifier (PK)
  LeaveApprovedBy1 uniqueidentifier (FK - tblStaffMembers)
  LeaveApprovedBy2 uniqueidentifier (FK - tblStaffMembers)

The business rule says: a staff member has "many" leave applications and a leave application belongs to a single staff member. Each application requires the approval of 2 staff members (managers) before it is "approved".

How would I map the above using EF so that a single staff member has a "many" leave applications (working already) and a leave application is mapped back to a staff member whom approved it for the first approval and then again for the seond approval. If I use the one mapping for "ApprovedBy1" only then EF is happy and all works as expected. The moment I add the second approval mapping EF struggles with the SQL queries it generates.

I am not sure how to tell EF to map back to the StaffMembers table to specify whom approved the application at level 1 and whom approved it at level 2. It almost ends up being a many to many relationship.

Any ideas?

score 0 · Answer 1 · answered Sep 27 '13 at 20:07

0

You are looking for the inverse property, which is the property at the other end of an association. In EF, there are two way to mark a property as inverse.

Data annotations: InversePropertyAttribute.
Fluent mapping

As you already have fluent mapping I'll show you how you'd do it there:

HasOptional(x => x.ApprovedBy1).WithMany(x => x.LeaveApps)
                               .HasForeignKey(s => s.StaffId);
HasOptional(x => x.ApprovedBy2).WithMany()
                               .Map(m => m.MapKey("LeaveApprovedBy2"));

The HasOptional(...).WithMany(...) pair is a way to map inverse properties. Coming from the other side you can use e.g. HasMany(....).WithOptional(...).

answered Sep 27 '13 at 20:07

Gert Arnold

105,341
31
202
291

Thanks for your reply. I have tried this, but the issue still remains where the initial mapping (ApprovedBy1) refers back to the actual staff member whom applied for leave. They cannot approve their own. – QuentinInSA Oct 02 '13 at 09:59
Essentially there are 3 staff members involved here: 1) The actual person asking for leave [Staff Table (parent) - Field: StaffID = Leave Table (child) - Field: StaffID] 2) The first "manager" approving the leave [Leave Table (Parent) - Field: ApprovedBy1 = Staff Table (Child) - Field: StaffID], 3) The second Manager approving the the leave [Leave Table (Parent) - Field: ApprovedBy2 = Staff Table (Child) - Field: StaffID] – QuentinInSA Oct 02 '13 at 10:12
I'm not sure if you're describing business logic here or a mapping problem. Maybe you should add it to your question to make it more clear. – Gert Arnold Oct 03 '13 at 07:12
Hi Gert, I updated the bottom seconf of the question to try and clarify it a bit more. Essentially it is a business rule, that needs to be stored in the DB, so it land across both domains. I need to know whom approved it first and whom second, but at the end of the day, every "person" involved is stored in the same table and linked under the child "leave Applications" table. – QuentinInSA Oct 14 '13 at 07:16
If I understand you well, you need a third mapping besides your "//Child Relationships" mappings that associates StaffId with LeaveApps and the business rule is `StaffId != LeaveApprovedBy1 != LeaveApprovedBy2`. Does that make sense? – Gert Arnold Oct 14 '13 at 20:08
If I had to do a SQL Query it would be: Select S1.StaffName as PersonOnLeave, A1.StaffName as ApprovedBy1, A2.StaffName as ApprovedBy2 From tblStaffMembers as S1 inner join tblLeaveApps as L on L.StaffID = S1.StaffID left outer join tblStaffMembers as A1 on A1.StaffID = L.ApprovedBy1 left outer join tblStaffMembers as A2 on A2.StaffID = L.ApprovedBy2 – QuentinInSA Oct 16 '13 at 06:28

Entity Framework Parent Child - Child refers to parent more than once

1 Answers1