CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-5ef-fluent-api
McArthey
McArthey

Reputation: 1646

Fluent API and building a 1:* relationship

I have two tables that have a 1 to 1..* relationship (meaning there will always be a record in the 'Status' table for a specific ID.

|Area|                |      Status     |
------                -------------------
|[Key]  ID |  ---->   |[Key]     ID     |
|      Name|          |[Key] Start Date |
                      |      End Date   |

I have the relationships built in my datalayer as follows.

Area

HasMany(s => s.Statuses)
    .WithRequired()
    .HasForeignKey(s => s.Id);

Status

HasRequired(a => a.Area)
   .WithMany(s => s.Statuses)
   .HasForeignKey(s => s.Id);

I have a generic 'AllIncluding' method that I use to gather the associated data. in the debugger I can view the query and copy/paste to my Oracle database and execute. It works as I'd expect and returns the appropriate number of rows.
The issue is that after execution in the debugger I browse through the recordset and find an entirely different set of data (a reduced set).

My thought is that it's because I have the keys defined as described in the diagram. I can create errors regarding ... the upper bound of the multiplicity of the Dependent Role must be '1' and also modify the recordset returned in the debugger by simply modifying the key on the Status table. This seems to indicate that the record relationship is attempting to remove duplicates in the Status table to create the Join? I have attempted to recreate this in Oracle by doing a group by trunc(start_date) or other similar queries but cannot hit the exact rows returned result.
It has only confirmed my suspicion that the mapping and relationship defined in Fluent API must be wrong, but I'm not sure how to properly represent this.

Ultimately I'd simply like to create a 1 -> 1..* relationship in Fluent API.

Thanks!

Upvotes: 1

Views: 161

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205539

It has only confirmed my suspicion that the mapping and relationship defined in Fluent API must be wrong

  1. Be very careful when mapping relationships with Fluent API. Specifically the With method overloads used must match exactly the presence / absence of a navigation property. WithRequired() and .WithRequired(a => a.Statuses) are totally different. Use the former when you don't have navigation property, and the later otherwise. In your case you used wrongly the parameterless overload.

  2. Always configure relationships in one place. The relationship is one thing involving two entities - there is no need to duplicate it in two places, and more importantly, it's error prone. Currently you have two definitions, one correct (for Status) and one incorrect (for Area). The net effect is that EF considers them to be two different relationships, which in turn leads to incorrect results.

So, either remove the code from Area and leave the code in the Status as is:

HasRequired(s => s.Area)
    .WithMany(a => a.Statuses)
    .HasForeignKey(s => s.Id);

or remove the code from Status and use the following in the Area:

HasMany(a => a.Statuses)
    .WithRequired(s => s.Area)
    .HasForeignKey(s => s.Id);

Upvotes: 1

Related Questions