How do I have class properties (with navigational props) as entity properties? Complex types won't do

Question

Basically I have an entity like:

public class Person {
 public int PersonId { get; set; }
 public string Name { get; set; }
 public Address Hometown { get; set; }
}

and a class like:

public class Address {
 public City City { get; set; }
 public string Province { get; set; }
}

What I want to accomplish is to have vertical join two classes and have a table with row:

TB_PERSON:
   PersonId PK
   Name
   City_id FK
   Province

Why I want this approach is, in my real project, I have same kind of data structure pattern occurring on multiple entries, in such case example would be the address class. It might easily appear in another entity.

Is it that much hard that I cannot find how to do this for days? Closest I can get is the complex types but they don't allow navigational properties in such case. I want to access and have my row data kind of structured and object oriented, thought EF would have a go. Any help is appreciated.

Answer 1

ComplexType SHOULD BE a solution but unfortunately:

Complex type cannot contain navigation properties. Source

List of workarounds:

Workaround with Table Splitting

public class Person
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public virtual Address Address { get; set; }
}

public class Address
{
    public Int32 ID { get; set; }
    public string Province { get; set; }
    public virtual City City { get; set; }

}

public class City
{
    public Int32 CityID { get; set; }
    public string Name { get; set; }
}

public class MappingContext : DbContext
{
    public DbSet<Person> Persons { get; set; }

    public DbSet<Address> Addresses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Address>()
            .HasKey(t => t.ID)
            .HasOptional(t => t.City)
            .WithMany()
            .Map(t => t.MapKey("CityID"));

        modelBuilder.Entity<Address>()
            .Property(t => t.ID)
            .HasColumnName("PersonID");

        modelBuilder.Entity<Person>()
            .HasKey(t => t.PersonID)
            .HasRequired(t => t.Address)
            .WithRequiredPrincipal();

        modelBuilder.Entity<Person>().ToTable("TB_PERSON");

        modelBuilder.Entity<Address>().ToTable("TB_PERSON");

        modelBuilder.Entity<City>()
            .HasKey(t => t.CityID)
            .ToTable("City");
    }
}

[Usage]

    using (var db = new MappingContext())
    {
        var person = db.Persons.FirstOrDefault();
        var cityName = person.Address.City.Name;

        var address = db.Addresses.FirstOrDefault();
        var personName = address.Person.Name;
    }

[Database]

    CREATE TABLE [dbo].[City](
        [CityID] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[TB_PERSON](
        [PersonId] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) NULL,
        [Province] [varchar](50) NULL,
        [CityID] [int] NULL
    ) ON [PRIMARY]

Workaround with Table Splitting + TPC inheritance (for reusable Address class)

TB_CUSTOMER is another table with address columns.

public class Person
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public virtual PersonAddress Address { get; set; }
}

public class Address
{
    public string Province { get; set; }
    public virtual City City { get; set; }

}

public class PersonAddress : Address
{
    public Int32 PersonID { get; set; }
    public virtual Person Person { get; set; }
}
public class CustomerAddress : Address
{
    public Int32 CustomerID { get; set; }
}

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public virtual CustomerAddress Address { get; set; }
}

public class City
{
    public Int32 CityID { get; set; }
    public string Name { get; set; }
}

public class MappingContext : DbContext
{
    public DbSet<Person> Persons { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<PersonAddress> PersonAddresses { get; set; }
    public DbSet<CustomerAddress> CustomerAddresses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PersonAddress>()
            .HasKey(t => t.PersonID)
            .HasOptional(t => t.City)
            .WithMany()
            .Map(t => t.MapKey("CityID"));

        modelBuilder.Entity<CustomerAddress>()
            .HasKey(t => t.CustomerID)
            .HasOptional(t => t.City)
            .WithMany()
            .Map(t => t.MapKey("CityID"));

        modelBuilder.Entity<Person>()
            .HasRequired(t => t.Address)
            .WithRequiredPrincipal(t => t.Person);

        modelBuilder.Entity<Customer>()
            .HasRequired(t => t.Address)
            .WithRequiredPrincipal();

        modelBuilder.Entity<Person>().ToTable("TB_PERSON");
        modelBuilder.Entity<PersonAddress>().ToTable("TB_PERSON");

        modelBuilder.Entity<Customer>().ToTable("TB_CUSTOMER");
        modelBuilder.Entity<CustomerAddress>().ToTable("TB_CUSTOMER");

        modelBuilder.Entity<City>()
            .HasKey(t => t.CityID)
            .ToTable("City");
    }
}

Workaround with IAddress

public class Person : IAddress
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public string Province { get; set; }
    public virtual City City { get; set; }

    [NotMapped]
    public IAddress Address { get { return this; } }
}

public interface IAddress
{
    string Province { get; set; }
    City City { get; set; }

}

public class City
{
    public Int32 CityID { get; set; }
    public string Name { get; set; }
}

public class MappingContext : DbContext
{
    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Person>()
            .HasKey(t => t.PersonID)
            .HasOptional(t => t.City)
            .WithMany()
            .Map(t => t.MapKey("CityID"));

        modelBuilder.Entity<Person>().ToTable("TB_PERSON");

        modelBuilder.Entity<City>()
            .HasKey(t => t.CityID)
            .ToTable("City");
    }
}

Answer 2

There are also 2 more workaround (not solutions) in addition to Table splitting.

Inheritance

Create Address class and inherit from it in every class that should have an address.
Address properties are mixed with other properties (so actually I think I would not apply this solution in your case).

1-1 relationship
(or n-1 relationship if more entities can share same address)

Model:

public class ClassA
{
    public int Id { get; set; }
    public string Description { get; set; }
    public virtual ClassB ClassB { get; set; }
}

public class ClassB
{
    public int Id { get; set; }
    public string Description { get; set; }
    public virtual ClassA ClassA { get; set; }
}

The context:

class Context : DbContext
{
    public Context(DbConnection connection)
        : base(connection, false)
    { }

    public DbSet<ClassA> As { get; set; }
    public DbSet<ClassB> Bs { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ClassB>().HasOptional(c => c.ClassA).WithOptionalDependent(c => c.ClassB);
    }
}

DDL Statements:

ExecuteNonQuery==========
CREATE TABLE [ClassAs] (
 [Id] int not null identity(1,1)
, [Description] text null
);
ALTER TABLE [ClassAs] ADD CONSTRAINT [PK_ClassAs_9cd06620] PRIMARY KEY ([Id])
ExecuteNonQuery==========
CREATE TABLE [ClassBs] (
 [Id] int not null identity(1,1)
, [Description] text null
, [ClassA_Id] int null
);
ALTER TABLE [ClassBs] ADD CONSTRAINT [PK_ClassBs_9cd06620] PRIMARY KEY ([Id])
ExecuteNonQuery==========
CREATE INDEX [IX_ClassA_Id] ON [ClassBs] ([ClassA_Id])
ExecuteNonQuery==========
ALTER TABLE [ClassBs] ADD CONSTRAINT [FK_ClassBs_ClassAs_ClassA_Id] FOREIGN KEY ([ClassA_Id]) REFERENCES [ClassAs] ([Id])

In this second case you can remove ClassB.ClassA navigation property so you can share ClassB across multiple types. The issue here is that you have 2 tables