I'm in a situation where I have a table called Elements
. Now I'm creating a table called Divergences
that will store basically pairs of Elements
. The purpose of Divergence
is to check if two Elements
have diverging answers.
Element Divergence
--------- ----------
ElementId ElementId1
ElementId2
In the above table schema, ElementId1
and ElementId2
are Foreign Keys mapping to ElementId
in Elements
table and form the composite primary key for the Divergence
table.
I use Database First approach where I create the tables in SQL Server Management Studio and later I do a Update Model from Database...
in Entity Framework Designer.
The problem I'm facing is that when EF Designer generates the model it creates 2 sets ICollection<Element>
inside the Element
class namely Elements
and Elements1
.
This doesn't give me a Divergences
DbSet.
What I'd want to have is a Divergence
class where I'd do something like this in code:
Divergence d = new Divergence();
d.Element1 = element1;
d.Element2 = element2;
Database.Divergences.Add(d);
Database.SaveChanges();
and later on:
Element e = Database.Elements.Single(e => e.ElementId == 7);
var divergences = e.Divergences;
I tried adding a new column to the Divergence
table like this:
Element Divergence
--------- ------------
ElementId DivergenceId
ElementId1
ElementId2
This correctly leads to a 1 <-> *
relationship in Entity Framework Designer. I finally get a Divergences
DbSet but DivergenceId
property is useless in the code and I still get the 2 sets in the Element
class. It's important to note that ElementId1
and ElementId2
still form the composite primary key.
What do you think is the correct way of mapping this specific situation? Thanks for the input.
Instead of...
Divergence d = new Divergence();
d.Element1 = element1;
d.Element2 = element2;
Database.Divergences.Add(d);
Database.SaveChanges();
... you could actually use:
element1.Elements = new List<Element>();
// if that collection isn't already instantiated, for example in the constructor
element1.Elements.Add(element2);
Database.SaveChanges();
This will create exactly the same SQL INSERT
statements to the link table without the need of having a Divergence
entity. (Change tracking will recognize that you changed the relationship by adding an item to the collection and infer the necessary SQL commands from this change. element1
and element2
must be attached to the context in state Unchanged
, but that is also required for your original code in order to work correctly.)
Also, instead of ...
Element e = Database.Elements.Single(e => e.ElementId == 7);
var divergences = e.Divergences;
... you can fetch the columns from the Divergences
table like so:
var divergences = Database.Elements.Where(e => e.ElementId == 7)
.SelectMany(e1 => e1.Elements.Select(e2 => new
{
ElementId1 = e1.ElementId,
ElementId2 = e2.ElementId,
}))
.ToList();
So, you will get your desired results without the Divergence
entity and honestly I would use it this way. I'm not aware of a way to force EF to create that entity with database first approach, unless you introduce some artificial additional column like you did. If there is a way it is probably a hack or more difficult to achieve and maintain than just working with EF's default, that is without a Divergence
entity.
However, you could consider to remove one of the collections (just delete it in the model surface). Having both is a bit confusing in this model in my opinion. Or at least rename them to SourceElements
and TargetElements
for example :)