Issue with EF CF and Existing One-to-One Relationship

Question

I am using an existing database with EF Code First and using the modelBuilder to configure. I have two tables where a SESSION can have a SUBJECT, classes are as such:

public class SessionItem {
   [Key]
   public int SessionId { get;set; }
   // Other Values
   public int Subject_ID { get;set; }

   public virtual Subject Subject { get;set; }
}

public class SubjectItem {
   [Key]
   public int Subject_ID { get;set; }
   // Other Values

   public virtual SessionItem Session { get;set; }
}

And then the modelBuilder code is:

modelBuilder.Entity<SessionItem>().ToTable("tblTblSessions");
modelBuilder.Entity<Subject>().ToTable("tblTblSubjects");
modelBuilder.Entity<SessionItem>()
   .HasOptional<Subject>(u => u.Subject)                     
   .WithOptionalDependent(c => c.Session).Map(p => p.MapKey("Subject_ID"));

This failed at first until I removed Subject_ID from the SessionItem class, then I got the error: A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object. This is a non-recoverable error.

Any idea where I have gone wrong?

Is the `Subject_ID` column not unique in the database, or why does the query return "more than one related object"? Do you really want a one-to-one relationship or a one-to-many? — Slauma, Jul 23 '12 at 14:02
It is unique, essentially there are a list of subjects and each session can have one subject. I may have the modelBuilder code totally wrong, I'm new to using it! — user1166905, Jul 23 '12 at 14:09

score 2 · Answer 1 · answered Jul 23 '12 at 13:50

2

Try this:

public class SessionItem 
{
   [Key]
   public int SessionId { get;set; }
   // Other Values
   [ForeignKey("Subject")]
   public int? Subject_ID { get;set; }

   [ForeignKey("Subject_ID")]    
   public virtual SubjectItem Subject { get;set; }
}

You need to make Subject_ID foreign key nullable.

answered Jul 23 '12 at 13:50

Paul Fleming

24,238
8
76
113

Do I still need my link in modelBuilder or remove this? – user1166905 Jul 23 '12 at 13:54
@user1166905. Use either. The important point is the nullable id `public int? Subject_ID` – Paul Fleming Jul 23 '12 at 13:59
Removing the modelBuilder code that tries to do the link and using the above I got this: Multiplicity is not valid in Role 'SessionItem_Subject_Source' in relationship 'SessionItem_Subject'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'. – user1166905 Jul 23 '12 at 14:13
I changed Subject class to make Sessions an ICollection then added the following to modelBuilder: modelBuilder.Entity() .HasOptional(u => u.Subject) .WithMany(s => s.Sessions) .HasForeignKey(e => e.Subject_ID); This now gets passed previous error but goes wrong loading the Sessions on the Subject (probably because there are 0000's) – user1166905 Jul 23 '12 at 14:31
I removed the navigation from Subject and put: modelBuilder.Entity() .HasOptional(u => u.Subject) .WithMany() .HasForeignKey(e => e.Subject_ID); Now it works fine – user1166905 Jul 23 '12 at 15:00
1

+1 for fairness as your code was already 50% of the solution. – Slauma Jul 23 '12 at 15:20

score 2 · Accepted Answer · answered Jul 23 '12 at 14:39

Unfortunely one-to-one foreign key associations are not supported with Entity Framework because EF doesn't know what a unique key constraint is (that your Subject_ID column in the Session table apparently has).

You must workaround this by mapping the relationship as one-to-many. Follow the mapping in @flem's answer for the SessionItem entity and for the SubjectItem entity either remove the public virtual SessionItem Session { get;set; } altogether or replace it by

public virtual ICollection<SessionItem> Sessions { get; set; }

You don't need the mapping with Fluent API anymore for this relationship, or if you want, it should be:

modelBuilder.Entity<SessionItem>()
    .HasOptional(se => se.Subject)                     
    .WithMany() // or WithMany(su => su.Sessions)
    .HasForeignKey(se => se.Subject_ID);

When you add items to that collection you must ensure in your business logic that you don't add more than one item because you can't have more than one row with the same Subject_ID in your database due to the unique key constraint. When you load a subject from the database including the sessions the session collection is either empty or has one single element, but not more.

This was what I did to get it to work, I removed the navigation property in the Subject as this link wasn't required. Not sure why this didn't appear before I put my fix above. — user1166905, Jul 23 '12 at 15:09
This answer is incorrect in asserting one-to-one relationships are not possible with Entity Framework: http://stackoverflow.com/a/10837964/176877 — Chris Moschini, Mar 30 '13 at 08:29
@ChrisMoschini: I said that **one-to-one foreign key** associations are not supported, not that **one-to-one** associations are not supported. Mortezza whose blog post you've linked in your own answer says it too: "*Code First (and EF in general) does not natively support one-to-one foreign key associations*". Supported are **one-to-one shared primary key** associations what you have configured in your answer. You might want to read his post again and the second one: http://weblogs.asp.net/manavi/archive/2011/04/14/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations.aspx — Slauma, Mar 30 '13 at 13:23

Chris Moschini · Answer 3 · 2013-03-30T21:19:28.513

1

[Table("tblTblSessions")]
public class SessionItem {
    [Key]
    public int SessionId { get; set; }

    public virtual SubjectItem Subject { get; set; }
}

[Table("tblTblSubjects")]
public class SubjectItem {
    [Key, ForeignKey("Session")]
    public int Subject_ID { get; set; }

    public virtual SessionItem Session { get; set; }
}

One-to-one relationship between SubjectItem and SessionItem, and you can get rid of all your modelBuilder code - all the table naming and one-to-one mapping you were doing is taken care of with the attributes and properties above.

Edit: Fixed a typo and marked the Dependent side of the one-to-one.

edited Mar 30 '13 at 21:19

answered Mar 30 '13 at 08:36

Chris Moschini

36,764
19
160
190

Did you test it? I did. First: It doesn't compile. Second: If you fix the obvious bug to make it compile (`public virtual SubjectItem Subject { get; set; }`) it throws an exception that the multiplicity at one end must be "*" (many). Reason as I said: One-to-one foreign key associations are not supported by EF. – Slauma Mar 30 '13 at 13:35
@Slauma I have my own code with similar domain models, but not identical names, so I apologize for the typo (and perhaps you should apologize for your tone). I've added the above classes to a test project and updated the example above. – Chris Moschini Mar 30 '13 at 21:19
+1 because it works now after correction :) But it's a shared primary key association. Who knows if that is sufficient for the OP. Shared primary keys are more limited than FK one-to-one associations (for example you can't assign a `Subject` to another `Session`, only the one that has the same key) and what he seemed to try in the question is a FK one-to-one. About the tone: What do you expect? You write a false statement under my answer, issue a downvote based on that and then wrote a wrong answer. That it didn't compile was pedantic, only point 2 was important. I was angry, sorry... – Slauma Mar 30 '13 at 22:00
1

BTW: The fact that EF does not support foreign key one-to-one associations is only a consequence of the fact that it doesn't support unique key constraints (and it still won't in EF 6) which is really a big big limitation and number 3 on the top feature request list: http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/1050579-unique-constraint-i-e-candidate-key-support – Slauma Mar 30 '13 at 22:03
@Slauma Great, thanks for helping me improve the answer. I'm in a bit of a white castle here because we build apps from scratch, where we needn't embrace existing databases, so not supporting that specific way of handling one-to-one doesn't affect us. That said, even working against an existing db should still be possible by copying data over to tables suitable to EF without data loss, so I can see why the feature might have stayed low in priority at Microsoft. – Chris Moschini Mar 30 '13 at 23:48
Well, it's not so much a question of existing or new databases but of business requirements. Shared primary key associations are more specific and limited (makes the relationship kind of "immutable"). Actually they are a special case of foreign key one-to-one associations (= FK with unique constraint), namely the special case where the FK is identical with the dependent's PK. – Slauma Mar 31 '13 at 18:44
@Slauma Well the one business case I can't solve with EF is one where I have a 1:1 both sides optional (because shared PK requires a dependent relationship); I have several of these in deployed applications which I solve by having no FK constraint whatsoever. I can still query and join them easily and this can actually make deletes easier in many cases because of a smaller number of dependencies/cascades to worry about. – Chris Moschini Mar 31 '13 at 18:57

Issue with EF CF and Existing One-to-One Relationship

3 Answers3