Cover by Rowan Miller, Julia Lerman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

O'Reilly logo

Working with Foreign Keys

So far we’ve just looked at relationships where there isn’t a foreign key property in your class. For example, Lodging just contains a reference property that points to Destination, but there is no property to store the key value of the Destination it points to. In these cases, we have seen that Code First will introduce a foreign key in the database for you. But now let’s look at what happens when we include the foreign key property in the class itself.

In the previous section you added some configuration to make the Lodging to Destination relationship required. Go ahead and remove this configuration so that we can observe the Code First conventions in action. With the configuration removed, add a DestinationId property into the Lodging class:

public int DestinationId { get; set; }

Once you have added the foreign key property to the Lodging class, go ahead and run your application. The database will get recreated in response to the change you just made. If you inspect the columns of the Lodgings table, you will notice that Code First has automatically detected that DestinationId is a foreign key for the Lodging to Destination relationship and is no longer generating the Destination_DestinationId foreign key (Figure 4-3).

Lodgings with FK after DestinationId is added to the class

Figure 4-3. Lodgings with FK after DestinationId is added to the class

As you might expect by now, Code First has a set or rules it applies to try and locate a foreign key property when it discovers a relationship. The rules are based on the name of the property. The foreign key property will be discovered by convention if it is named [Target Type Key Name], [Target Type Name] + [Target Type Key Name], or [Navigation Property Name] + [Target Type Key Name]. The DestinationId property you added matched the first of these three rules. Name matching is case-insensitive, so you could have named the property DestinationID, DeStInAtIoNiD, or any other variation of casing. If no foreign key is detected, and none is configured, Code First falls back to automatically introducing one in the database.

There’s something else interesting that happens when you add the foreign key property. Without the DestinationId foreign key property, Code First convention allowed Lodging.Destination to be optional, meaning you could add a Lodging without a Destination. If you check back to Figure 2-1 in Chapter 2, you’ll see that the Destination_DestinationId field in the Lodgings table is nullable. Now with the addition of the DestinationId property, the database field is no longer nullable and you’ll find that you can no longer save a Lodging that has neither the Destination nor DestinationId property populated. This is because DestinationId is of type int, which is a value type and cannot be assigned null. If DestinationId was of type Nullable<int>, the relationship would remain optional. By convention, Code First is using the nullability of the foreign key property in your class to determine if the relationship is required or optional.

Specifying Unconventionally Named Foreign Keys

What happens when you have a foreign key, but it doesn’t follow Code First convention?

Let’s introduce a new InternetSpecial class that allows us to keep track of special pricing for the various lodgings (Example 4-7). This class has both a navigation property (Accommodation) and a foreign key property (AccommodationId) for the same relationship.

Example 4-7. The new InternetSpecial class

using System;
namespace Model
  public class InternetSpecial
    public int InternetSpecialId { get; set; }
    public int Nights { get; set; }
    public decimal CostUSD { get; set; }
    public DateTime FromDate { get; set; }
    public DateTime ToDate { get; set; }

    public int AccommodationId { get; set; }
    public Lodging Accommodation { get; set; }

Lodging will need a new property to contain each lodging’s special prices:

public List<InternetSpecial> InternetSpecials { get; set; }

Code First can see that Lodging has many InternetSpecials and that InternetSpecials has a Lodging (called Accommodation). Even though there’s no DbSet<InternetSpecial>, InternetSpecial is reachable from Lodging and will therefore be included in the model.


You’ll learn more about how the model builder finds or ignores entities in Chapter 5.

When you run your application again, it will create the table shown in Figure 4-4. Not only is there an AccommodationId column, which is not a foreign key, but there is also another column there which is a foreign key, Accommodation_LodgingId.

InternetSpecials appears to have two foreign keys

Figure 4-4. InternetSpecials appears to have two foreign keys

You’ve seen Code First introduce a foreign key in the database before. As early as Chapter 2, you witnessed the Destination_DestinationId field added to the Lodgings table because Code First detected a need for a foreign key. It’s done the same here. Thanks to the Accommodation navigation property, Code First detected a relationship to Lodging and created the Accommodation_LodgingId field using its conventional pattern. Code First convention was not able to infer that AccommodationId is meant to be the foreign key. It simply found no properties that matched any of the three patterns that Code First convention uses to detect foreign key properties, and therefore created its own foreign key.

Fixing foreign key with Data Annotations

You can configure foreign key properties using the ForeignKey annotation to clarify your intention to Code First. Adding ForeignKey to the AccommodationId, along with information telling it which navigation property represents the relationship it is a foreign key for, will fix the problem:

public int AccommodationId { get; set; }
public Lodging Accommodation { get; set; }

Alternatively, you can apply the ForeignKey annotation to the navigation property and tell it which property is the foreign key for the relationship:

public int AccommodationId { get; set; }
public Lodging Accommodation { get; set; }

Which one you use is a matter of personal preference. Either way, you’ll end up with the correct foreign key in the database: AccommodationId, as is shown in Figure 4-5.

AccommodationId correctly identified as the foreign key

Figure 4-5. AccommodationId correctly identified as the foreign key

Fixing foreign key with the Fluent API

The Fluent API doesn’t provide a simple way to configure the property as a foreign key. You’ll use the relationship API to configure the correct foreign key. And you can’t simply configure that piece of the relationship; you’ll need to first specify which relationship you want to configure (as you learned how to do earlier in this chapter) and then apply the fix.

To specify the relationship, begin with the InternetSpecial entity. We’ll do that directly from the modelBuilder, although you can certainly create an EntityTypeConfiguration class for InternetSpecial.

In this case, we’ll be identifying the relationship but not changing the multiplicity that Code First selected by convention. Example 4-8 specifies the existing relationship.

Example 4-8. Identifying the relationship to be configured

  .HasRequired(s => s.Accommodation)
  .WithMany(l => l.InternetSpecials)

What we want to change, however, is something about the foreign key that is also involved with this relationship. Code First expects the foreign key property to be named LodgingId or one of the other conventional names. So we need to tell it which property truly is the foreign key—AccommodationId. Example 4-9 shows adding the HasForeignKey method to the relationship you specified in Example 4-8.

Example 4-9. Specifying a foreign key property when it has an unconventional name

  .HasRequired(s => s.Accommodation)
  .WithMany(l => l.InternetSpecials)
  .HasForeignKey(s => s.AccommodationId);

This, too, will result in the database schema shown in Figure 4-5.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required