CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Nick
Nick

Reputation: 88

EF6 Many-to-Many on Non-Key Column

I am having trouble connecting a many-to-many relationship in EF6 using code first, then creating a pass-through association beyond that.

There are three classes: Person, Tag, and Passing.

Each Person has an optional Bib.

Each Tag has an optional Bib, not unique.

Each Passing has a required TagId.

I want to access all Passings linked to a Person by getting all Tags with the same Bib, then getting all Passings associated with each of those Tags.

I have tried using the DBModelBuilder in my DBContext class but can't get it to work properly, and EF6 seems to try to generate an intermediate table anyways, which seems unnecessary.

public class Person
{
    [Key]
    public int PersonId { get; set; }
    ...
    public string Bib { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
    public virtual ICollection<Passing> Passings
}

public class Tag
{
    [Key]
    public string TagId { get; set; }
    public string Bib { get; set; }
    public virtual ICollection<Passing> Passings { get; set; }
    public virtual Person Person { get; set; }
}

public class Passing
{
    [Key]
    public int PassingId { get; set; }
    ...
    public string TagId { get; set; }
    public virtual Tag Tag { get; set; }
}

Upvotes: 3

Views: 136

Answers (2)

jjj
jjj

Reputation: 4997

Entity Framework uses navigation properties to represent database relationships. If you don't want an additional table, what you have here is not a database relationship since keys aren't involved.

You should be able to use some kind of function (or extension function) to get what you want:

IQueryable<Passing> PersonPassings(YourContext db, Person p)
{
    return db.Passings.Where(pa => pa.Tag.Bib == p.Bib);
}

On the other hand, if you want to create a proper relationship, you'll need an intermediate Bibs table to connect Person and Tag.

Upvotes: 1

Antoine Pelletier
Antoine Pelletier

Reputation: 3326

IT IS necessary, when you have a * to * multiplicity into a table, it automaticly creates another table that links these, elseway you cannot put an infinite and variable number of foraign key in one of your table

Upvotes: 1

Related Questions