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.
3 Answers
Answers 1
I had the same struggle. Unfortunately:
Complex type cannot contain navigation properties.
You can try using "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 PersonID { get; set; } public string Province { get; set; } public virtual Person Person { 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.PersonID) .HasOptional(t => t.City) .WithMany() .Map(t => t.MapKey("CityID")); modelBuilder.Entity<Person>() .HasRequired(t => t.Address) .WithRequiredPrincipal(t => t.Person); 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] Example for reusable Address class ( Table Splitting + TPC )
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"); } } Answers 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
Answers 3
Almost every complex navigation is possible with fluent api.
Google These: Fluent Api and EntityTypeConfiguration
0 comments:
Post a Comment