entity framework - Difference between .WithMany() and .WithOptional()?

Question

Below are two similar fluent API configurations:

WithMany()

modelBuilder.Entity<Country>()
            .HasRequired(cou => cou.Currency)
            .WithMany()
            .WillCascadeOnDelete(false); 

WithOptional()

modelBuilder.Entity<Country>()
            .HasRequired(cou => cou.Currency)
            .WithOptional()
            .WillCascadeOnDelete(false);

What I am trying to express here is: Every Country requires a concrete Currency, but a Currency can be assigned to zero, one or many Countries.

Which of the above statements would I have to use? Or in other words: What exactly is the difference between .WithMany() and .WithOptional() operators?

Answer 1

If your model would look like this:

public class Country
{
    public int CountryId { get; set; }
    public Currency Currency { get; set; }
}

public class Currency
{
    public int CurrencyId { get; set; }
}

then ...

modelBuilder.Entity<Country>()
            .HasRequired(cou => cou.Currency)
            .WithOptional()
            .WillCascadeOnDelete(false);

... creates a foreign key relationship in the database where CountryId in the Countries table is primary key and foreign key to the CurrencyId of the Currencies table at the same time, so the Countries table has only one single column CountryId. A Currencies record can live without a related Countries record. But if a Currencies record has a related Countries record then not more than one because the foreign key is CountryId which is the primary key at the same time and can therefore only be in one record. So the relationship Currencies -> Countries is 1-to-0...1.

The other example ...

modelBuilder.Entity<Country>()
            .HasRequired(cou => cou.Currency)
            .WithMany()
            .WillCascadeOnDelete(false);

... creates a second column CurrencyId in the Countries table of the database which is non-nullable and is a foreign key to the CurrencyId of the Currencies table. So here it is possible that a Currencies record has no related Countries record or one or more than one because the foreign key is now another column, not identical with the primary key. Therefore more than one row in the Countries table may have the same foreign key. The relationship Currencies -> Countries here is 1-to-0...n.

Edit

If you take the following code for the two differently configured models ...

Country country1 = new Country();
Country country2 = new Country();
Currency currency = new Currency();

country1.Currency = currency;
country2.Currency = currency;

context.Countries.Add(country1);
context.Countries.Add(country2);

context.SaveChanges();

... then the second case (.WithMany) works: We get two new Countries and one Currency in the database.

However a bit strange is that in the second case (.HasOptional) only the first Country is stored, the second is simply ignored. Actually I had expected to get an exception. I am not sure if that has to be considered as a bug.

Edit2

Changing the order in the example above to ...

context.Countries.Add(country1);
context.Countries.Add(country2);

country1.Currency = currency;
country2.Currency = currency;

... throws the expected exception in the ".HasOptional" case.