EF6 Many-to-many in non-key column
I am having trouble connecting many-to-many relationships in EF6 using code and then creating an end-to-end association.
There are three classes: Person, Tag, and Passing.
Each person has an optional Bib.
Each tag has an optional Bib, not unique.
Each pass has a required TagId.
I want to access all the passes associated with a Person by getting all the tags with the same Bib and then getting all the passes associated with each of those tags.
I've tried using DBModelBuilder in my DBContext class but can't get it to work correctly and EF6 seems to be trying to create an intermediate table anyway, 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; }
}
source to share
Entity Framework uses navigation properties to represent database relationships. If you do not need an additional table, then you have no relation to the database here, since the keys are not involved.
You can 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 the correct relationship, you need an intermediate table Bibs
to connect Person
and Tag
.
source to share