0

I created the following model in the Entity Framework 5 Model Designer in Visual Studio 2012:

enter image description here

Then I generated the database from the model, which resulted in the following tables in my database:

enter image description here

Please help me understand why Entity Framework is generating a one-to-many relationship for a one-to-zero-or-one association.

Update #1

Furthermore, if I change the association from 1:0..1 to 1:1 like this:

enter image description here

Then not only is there still no one-to-one relationship in the database, but now the one-to-many relationship is flipped around, which seems even more weird to me:

enter image description here

Update #2

In response to Mystere Man's comment and answer, the structure I'm expecting to see in SQL Server, which is a valid 1:0..1 relationship is as follows:

enter image description here

Furthermore, I am able to get this working exactly as intended with the following Code First fluent mapping:

public class UserMap : EntityTypeConfiguration<User>
{
    public UserMap()
    {
        ToTable("Users");

        HasKey(t => t.UserId);

        Property(t => t.UserId)
            .HasColumnName("UserId");

        Property(t => t.Name)
            .HasColumnName("Name")
            .IsRequired()
            .HasMaxLength(50);

        Property(t => t.EmailAddress)
            .HasColumnName("EmailAddress")
            .IsRequired()
            .HasMaxLength(254);

        Property(t => t.CreatedDateTime)
            .HasColumnName("CreatedDateTime");

        HasOptional(t => t.Subscription)
            .WithRequired(t => t.User);
    }
}

public class SubscriptionMap : EntityTypeConfiguration<Subscription>
{
    public SubscriptionMap()
    {
        ToTable("Subscriptions");

        HasKey(t => t.SubscriptionId);

        Property(t => t.SubscriptionId)
            .HasColumnName("SubscriptionId");

        Property(t => t.TypeValue)
            .HasColumnName("TypeValue");

        Property(t => t.CreatedDateTime)
            .HasColumnName("CreatedDateTime");

        Property(t => t.ExpiresDateTime)
            .HasColumnName("ExpiresDateTime");

        HasRequired(t => t.User)
            .WithOptional(t => t.Subscription);
    }
}

So, I know it's possible to achieve this behavior with Code First Entity Framework. My question is why it's not possible to do it with the Model First approach.

What's going on here and why?

Thanks!

Tim S
  • 2,309
  • 16
  • 23

1 Answers1

2

SQL does not have a way to define a true 1:1 or 1:0..1 data model, except when both entities have the same primary key. Even so, you can't have a 1:1 because you can't insert records into more than one table in a single statement, so the data model has to allow 1:0..1 by virtue of there being a intermediate state where one record will exist without the other.

The only way to do this in SQL is to do exactly what EF is doing here, create a 1:*, and then impose constraints ensure uniqueness (such as a Unique Constraint). However, EF doesn't support constraints (you would have to create them manually) so it's possible to insert more than one record and violate your model.

EDIT:

Since you've clarified that you're talking about Model First, and that you are looking for a shared primary key, then here's what you have to do.

Right click on the Association, and create a referential constraint between Subscription and User. This will then cause EF to generate the 1:0..1 relationship you are looking for.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • I've updated my question to illustrate that it is indeed possible to create a 1:0..1 relationship with a share primary key. They key, however, does not need to have the same name. Using the new example I provided in my update, it is possible to add a record to the Users table in one statement, and then a record to the Subscriptions table in the next (but not vice versa) without violating any constraints. I also showed how it's possible in EF Code First. – Tim S Oct 20 '13 at 22:43
  • @TimS - Notice in my answer I specifically said "except when both entities have the same primary key". From your question, it did not seem like you were trying to share a common primary key, which is why I phrased it the way I did. I would, however, suggest that naming these keys differently is confusing, and violates the EF naming conventions. You can of course override them as you've done. Since your original question didn't mention sharing a primary key, and you had different names, it wasn't obvious what you meant, and if We as humans can't figure it out, how would you expect EF to? – Erik Funkenbusch Oct 21 '13 at 00:44