Navigation property has been configured with conflicting multiplicities Fluent API

Question

I have some relational issues between two tables, one - to - many.

The whole concept is as follows:

Table 1: Country:
Besides it's own fields it also contains two ICollections where one points to a collection of regions and the other on cities.

Table 2: Region
Besides it's own fields, it also contains a CountryId and a ICollection of Cities.

Table 3: City
Besides it's own fields, it also contains a CountryId and a RegionId (where regionId can be nullable).

The principal idea is that a Country can have region(s) or/and city(ies).

This means that each foreign key from region/city to the CountryId cannot be null. But, the foreign key from City To Region is allowed to be null, since "some" Countries, in this case, regions is unnecessary.

Observe that all three tables is also referring, with an ICollection, to a middle table which in turn stores the relation between them.

Country Entity:

public class Country
{
    public Guid Id { get; set; }
    public int CountryId { get; set; }
    public Guid ComponentId { get; set; }

    public string NumCode { get; set; }
    public string Code2 { get; set; }
    public string Code3 { get; set; }

    public virtual ICollection<Region> Regions { get; set; }
    public virtual ICollection<City> Cities { get; set; }
    public virtual ICollection<AdvertGeography> AdvertsGeographies { get; set; }
}

public class CountryMap()
    {
        // Primary Key
        HasKey(t => t.Id);
        Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // Unique Index
        Property(e => e.ComponentId)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique_Component", 1) { IsUnique = true }));
        Property(e => e.CountryId)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique_Country", 1) { IsUnique = true }));

        // Properties

        // Table & Column Mappings
        ToTable("Country");

        Property(e => e.Id).HasColumnName("Id");
        Property(e => e.CountryId).HasColumnName("CountryId");
        Property(e => e.ComponentId).HasColumnName("ComponentId");
        Property(e => e.Code2).HasColumnName("Code2");
        Property(e => e.Code3).HasColumnName("Code3");
        Property(e => e.NumCode).HasColumnName("NumCode");

        // Relationships
        HasMany(t => t.Regions)
            .WithRequired(t => t.Country).WillCascadeOnDelete(false);

        HasMany(t => t.Cities)
            .WithRequired(t => t.Country).WillCascadeOnDelete(false);

    }

Region Entity:

public class Region
{
    public Region()
    {
        this.Cities = new HashSet<City>();
    }
    public Guid Id { get; set; }
    public Guid CountryId { get; set; }
    public virtual Country Country { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Office> Offices { get; set; }
    public virtual ICollection<City> Cities { get; set; }
    public virtual ICollection<OpenApplication> OpenApplications { get; set; }
    public virtual ICollection<Subscription> Subscriptions { get; set; }
    public virtual ICollection<AdvertGeography> AdvertsGeographies { get; set; }
}

public class RegionMap()
    {
        // Primary Key
        HasKey(t => t.Id);
        Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // Unique Index
        Property(e => e.Name)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique", 1) { IsUnique = true }));
        Property(e => e.CountryId)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique", 2) { IsUnique = true }));

        // Properties
        Property(e => e.Name).IsRequired().HasMaxLength(512);

        // Table & Column Mappings
        ToTable("Region");

        Property(e => e.Id).HasColumnName("Id");
        Property(e => e.Name).HasColumnName("Name");
        Property(e => e.CountryId).HasColumnName("CountryId");

        // Relationships
        HasMany(t => t.Subscriptions)
            .WithMany(t => t.Regions);

        HasMany(t => t.OpenApplications)
            .WithMany(t => t.Regions);

        HasMany(t => t.Offices)
            .WithRequired(t => t.Region);

        HasMany(t => t.Cities)
            .WithRequired(t => t.Region);
        
        HasRequired(t => t.Country).WithMany(t => t.Regions).WillCascadeOnDelete(false);
    }

City Entity:

public class City
{
    public Guid Id { get; set; }
    public Guid CountryId { get; set; }
    public virtual Country Country { get; set; }
    public Guid? RegionId { get; set; }
    public virtual Region Region { get; set; }
    public string Name { get; set; }
    public virtual ICollection<AdvertGeography> AdvertsGeographies { get; set; }
}

public class CityMap()
    {
        //Primary Key
        HasKey(e => e.Id);
        Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        //Unique Index
        Property(e => e.Name)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique", 1) { IsUnique = true }));
        

        //Properties
        Property(e => e.Name).IsRequired().HasMaxLength(512);
        
        //Table & Column Mappings
        ToTable("City");
        Property(e => e.Id).HasColumnName("Id");
        Property(e => e.Name).HasColumnName("Name");
        Property(e => e.CountryId).HasColumnName("CountryId");
        Property(e => e.RegionId).HasColumnName("RegionId");

        // Relationships
        HasRequired(t => t.Country).WithMany(t => t.Cities).WillCascadeOnDelete(false);
        HasOptional(t => t.Region).WithMany(t => t.Cities).HasForeignKey(t => t.RegionId);
    }    
/J

Anybody have an idea? Glad for any help

Answer 1

Actually I have solved it now. I missied this in RegionMap:

Quite trivial, but when you stare too long at your code, these things easily slips through.

HasMany(t => t.Cities) .WithRequired(t => t.Region);

Should be

HasMany(t => t.Cities) .WithOptional(t => t.Region);

Thanks anyway.

/J