CODEWITHSUNDEEP
c#many-to-manyentity-framework-5code-firstcross-reference
Alex
Alex

Reputation: 77329

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

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?

Upvotes: 2

Views: 4049

Answers (1)

Claies
Claies

Reputation: 22323

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.

Upvotes: 3

Related Questions