Entity Framework 4.2 code first fluent api mapping intermediate table

Question

How do I setup the mapping for these table relationships using code first and the fluent api?

I have a poco for all three entities created but most people don't have a poco for the "intermediate" table (PlanUser) so I couldn't find a good example to go off of. I need to tell EF to map the PlanUser.PlanCustomerId column to Plan.CustomerId but it either doesn't return the right results OR as in the current setup the mapping for Plan throws the error:

"The specified association foreign key columns 'CustomerId' are invalid. The number of columns specified must match the number of primary key columns."

enter image description here

public class PlanUserMap : EntityTypeConfiguration<PlanUser>
{
    public PlanUserMap()
    {
        this.ToTable("PlanUser");
        this.HasKey(c => new { c.CustomerId, c.PlanCustomerId });

        this.HasRequired(c => c.Plan).WithMany().HasForeignKey(x => x.CustomerId).WillCascadeOnDelete(false);
        this.HasRequired(c => c.Customer).WithMany().HasForeignKey(x => x.CustomerId).WillCascadeOnDelete(false);
    }
}

public class PlanMap : EntityTypeConfiguration<Plan>
{
    public PlanMap()
    {
        this.ToTable("Plan");
        this.HasKey(c => c.CustomerId);
        // the below line returns only 1 row for any given customer even if there are multiple PlanUser rows for that customer
        //this.HasMany(c => c.PlanUsers).WithRequired().HasForeignKey(c => c.PlanCustomerId); 
        // this throws an error
        this.HasMany(c => c.PlanUsers).WithMany().Map(m => m.MapLeftKey("PlanCustomerId").MapRightKey("CustomerId"));
    }
}

public partial class CustomerMap : EntityTypeConfiguration<Customer>
{
    public CustomerMap()
    {
        this.ToTable("Customer");
        this.HasKey(c => c.Id);
        this.HasMany(c => c.PlanUsers).WithRequired().HasForeignKey(c => c.CustomerId);
    }
}

@Slauma, sql profiler shows these queries being executed. The second one should include customer id 43 in addition to customer id 1 but it does not. I don't know why its not retrieving that second row.

exec sp_executesql N'SELECT 
[Extent1].[CustomerId] AS [CustomerId], 
[Extent1].[PlanCustomerId] AS [PlanCustomerId], 
[Extent1].[CreatedOnUtc] AS [CreatedOnUtc], 
[Extent1].[IsSelected] AS [IsSelected], 
[Extent1].[IsDeleted] AS [IsDeleted], 
[Extent1].[AccessRights] AS [AccessRights]
FROM [dbo].[PlanUser] AS [Extent1]
WHERE [Extent1].[CustomerId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=43

exec sp_executesql N'SELECT 
[Extent1].[CustomerId] AS [CustomerId], 
[Extent1].[Name] AS [Name], 
[Extent1].[PlanTypeId] AS [PlanTypeId], 
[Extent1].[OrderId] AS [OrderId], 
[Extent1].[CreatedOnUtc] AS [CreatedOnUtc], 
[Extent1].[IsActive] AS [IsActive]
FROM [dbo].[Plan] AS [Extent1]
WHERE [Extent1].[CustomerId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

Here is the C# code that causes the queries to execute:

    public List<Plan> GetPlans()
    {
        List<Plan> plans = new List<Plan>();

        // add each plan they have access rights to to the list
        foreach (var accessiblePlan in Customer.PlanUsers)
        {
            plans.Add(accessiblePlan.Plan);
        }

        return plans;
    }

score 3 · Answer 1 · edited May 23 '17 at 10:32

3

I think what you need is actually simpler than the mapping you tried:

public class PlanUserMap : EntityTypeConfiguration<PlanUser>
{
    public PlanUserMap()
    {
        this.ToTable("PlanUser");
        this.HasKey(pu => new { pu.CustomerId, pu.PlanCustomerId });

        this.HasRequired(pu => pu.Customer)
            .WithMany(c => c.PlanUsers)
            .HasForeignKey(pu => pu.CustomerId)
            .WillCascadeOnDelete(false);

        this.HasRequired(pu => pu.Plan)
            .WithMany(p => p.PlanUsers)
            .HasForeignKey(pu => pu.PlanCustomerId)
            .WillCascadeOnDelete(false);
    }
}

public class PlanMap : EntityTypeConfiguration<Plan>
{
    public PlanMap()
    {
        this.ToTable("Plan");
        this.HasKey(p => p.CustomerId);
    }
}

public partial class CustomerMap : EntityTypeConfiguration<Customer>
{
    public CustomerMap()
    {
        this.ToTable("Customer");
        this.HasKey(c => c.Id);
    }
}

I am not sure why you disable cascading delete. I probably wouldn't do this (i.e. I would remove the WillCascadeOnDelete(false) for both relationships) because the association entity PlanUser is kind of dependent of the other two entities.

Here are a bit more details about this kind of model (sometimes called "many-to-many relationship with payload"): Create code first, many to many, with additional fields in association table

edited May 23 '17 at 10:32

Community

1
1

answered Nov 01 '12 at 12:59

Slauma

175,098
59
401
420

Tried those changes but like the original mapping any given customer returns only 1 PlanUser. My current dataset for customer 43 should be returning 2 PlanUser rows. I used sql profiler and the queries are ignoring the second PlanUser row. I'd like to paste the queries in but this box has a retarded character limit. – TugboatCaptain Nov 01 '12 at 23:00
@StrandedPirate: Are all your navigation properties marked as `virtual`, i.e. `Customer.PlanUsers`, `PlanUser.Plan`, etc.? – Slauma Nov 01 '12 at 23:18
Yes they are. Should they not be? – TugboatCaptain Nov 01 '12 at 23:20
@StrandedPirate: They should, because your example query relies on lazy loading. Did you try eager loading: `var customer = context.Customers.Include(c => c.PlanUsers).Single(c => c.Id == 43)` and then checked in debugger how many elements `customer.PlanUsers` has? – Slauma Nov 01 '12 at 23:28
This did pull back the two records: var list = planUserRepository.Table.Include(a => a.Plan).Where(a => a.CustomerId == Customer.Id).ToList(); But I still want to know why my mapping setup isn't working. When we call _Customer.PlanUsers_ there is no user-defined linq query behind it, its simply getting all the rows that are available. At this point I've commented out all of the navigation properties, renamed the _Plan.CustomerId_ column to _Plan.PlanCustomerId_, and everything maps correctly but still only 1 row returned for the user. – TugboatCaptain Nov 02 '12 at 00:01

score 0 · Accepted Answer · answered Nov 02 '12 at 01:26

Ok, I figured it out. Thanks to @Slauma for the help! We are using the repository pattern with IOC(autofac). I shouldn't be calling Customer.PlanUsers in my repository service in the first place. Somewhere along the line we started resolving the Customer entity using IOC in the repository service to get at certain customer properties quickly and put it in a read-only variable. Inevitably this hosed us because instead of querying the entity repositories themselves we started with the Customer entity expecting it to be filled with everything we needed and its not pre-filled.

    public List<Plan> GetPlans()
    {
        List<Plan> plans = new List<Plan>();
        var list = _planUserRepository.Table.Where(a => a.CustomerId == Customer.Id).ToList();
        foreach (var planUser in list)
        {
            plans.Add(planUser.Plan);
        }
    }

Entity Framework 4.2 code first fluent api mapping intermediate table

2 Answers2