Add related data to cross reference table in Entity Framework 5 (Code-First)

Question

Let's say I have the following two models:

Person             N
Subscription       N

They are in a many-to-many relationship (Each Person can have multiple Subscriptions, and each Subscription can have multiple Persons), so Entity Framework creates a cross reference table:

PersonSubscriptions:  PersonId  |  SubscriptionId

If I wanted to record when each person started subscribing, it would make the most sense to have a Date column in the cross reference table:

PersonSubscriptions:  PersonId  |  SubscriptionId  |  SubscribedOn

How can I achieve this with Entity Framework and how would I query, say, to get all Persons that subscribed after day X to a given Subscription?

marc_s · Accepted Answer · 2024-09-03 03:50:52Z

3

In this situation, you no longer have a many-to-many relationship in a traditional sense, you instead have 2 distinct 1 to many relationships with a 3rd entity. In essence, you will have 3 classes to work with:

public class Person 
{
    public int PersonId { get; set; }
    public virtual IEnumerable<SubscriptionInfo> SubscriptionInfos { get; set; }
}

public class Subscription 
{
    public int SubscriptionId { get; set; }
    public virtual IEnumerable<SubscriptionInfo> SubscriptionInfos { get; set; }
}

public class SubscriptionInfo 
{
    public int PersonId { get; set; }
    public int SubscriptionId { get; set; }
    public DateTime SubscribedOn { get; set; }

    [RelatedTo(ForeignKey="PersonId")]
    Public virtual Person Person { get; set; }

    [RelatedTo(ForeignKey="SubscriptionId")]
    Public virtual Subscription Subscription { get; set; }
}

var db = new PeopleSubscribedEntities();
var subscription = db.Subscriptions.Find(1);
var people = subscription.SubscriptionInfos
                         .Select(si => si.Person 
                                       && si => si.SubscribedOn > someDate);

If Entity Framework were to treat this table as a many to many, you would lose the extra attributes.

edited Sep 3, 2024 at 3:50

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 24, 2013 at 21:21

Claies

22.4k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Thanks, Andrew! Would you expect your sample code to create the table as I outlined in my question? Or a separate SubscriptionInfo table?

Claies Over a year ago

I just referred to it as SubscriptionInfo in my samples, but if it were named PersonSubscriptions, it would create a table named that, with the 3 fields... I was mostly offering a direction to take the code, but the names can be changed, of course. With the class names I used, Entity Framework would create a SubscriptionInfo table instead of the default join table.

Collectives™ on Stack Overflow

Add related data to cross reference table in Entity Framework 5 (Code-First)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related