Devart Blog

Support of Many-to-One Mapping for Component Navigation Properties in Entity Developer

Posted by on May 24th, 2012

This article explains and gives a practical example of how support of сomponent navigation properties is implemented in Entity Developer for NHibernate.
Support of this functionality includes the possibility of detaching to a complex type for further reusability not only scalar entity properties but also navigation properties with subsequent customization of complete mapping at the level of private mapping of a certain property with type equal to complex type of a certain entity.

There are several ways of component navigation properties mapping in NHibernate:

  • Many-to-one;
  • One-to-many;
  • One-to-one;
  • Many-to-many.

In this article we will consider the most popular of them, i.e. many-to-one mapping of сomponent navigation properties. We will not focus on one-to-many and one-to-one types of сomponent navigation properties mapping, as these types of mapping are not very common, besides, their usage and customization have little difference from the example under consideration and so are quite comprehensible. Many-to-many mapping is considered in details in the article Support of Many-to-Many Mapping for Component Navigation Properties in Entity Developer.

Many-to-one mapping of component navigation properties

This type of mapping can be efficient in case there are several tables in the database referring to the same details table using a foreign key constraint, and also, possibly (but this is not a must), having some set of fields matching in meaning and structure. Or the same table contains several sets of fields or foreign keys similar in structure, describing respectively several data objects similar in structure (e.g. when the same table containing descriptions of companies includes several matching sets of fields representing addresses of head and shipping offices).

Then, after creating a model, it makes sense to move the similar set of the corresponding scalar and navigation properties into a separate complex type, and replace every such set in entities with a property having type equal to complex type and an appropriate customization of private mapping for this property.

Example:

The database contains the Orders and Suppliers tables, which have a foreign key constraint with the Countries table. The Orders and Suppliers tables have the same fields containing address description, and also a foreign key column associated with the table containing country names with a foreign constraint.

The script for tables creation for SQL Server DBMS is as follows:

CREATE SCHEMA ManyToOne;
GO

CREATE TABLE ManyToOne.Countries (
  CountryID int,
  CountryName nvarchar(15) COLLATE Cyrillic_General_CI_AS NOT NULL,
  CONSTRAINT PK_Countries PRIMARY KEY (CountryID)
)
GO

CREATE TABLE ManyToOne.Orders (
  OrderID int IDENTITY,
  Customer nvarchar(50) COLLATE Cyrillic_General_CI_AS Not NULL,
  Employee nvarchar(50) COLLATE Cyrillic_General_CI_AS Not NULL,
  OrderDate datetime NULL,
  RequiredDate datetime NULL,
  ShippedDate datetime NULL,
  Freight money NULL CONSTRAINT DF_Orders_Freight DEFAULT ((0)),
  ShipName nvarchar(40) COLLATE Cyrillic_General_CI_AS NULL,
  ShipAddress nvarchar(60) COLLATE Cyrillic_General_CI_AS NULL,
  ShipCity nvarchar(15) COLLATE Cyrillic_General_CI_AS NULL,
  ShipRegion nvarchar(15) COLLATE Cyrillic_General_CI_AS NULL,
  ShipPostalCode nvarchar(10) COLLATE Cyrillic_General_CI_AS NULL,
  ShipCountryID int NULL,
  CONSTRAINT PK_Orders PRIMARY KEY (OrderID),
  CONSTRAINT FK_Orders_Countries FOREIGN KEY (ShipCountryID) REFERENCES ManyToOne.Countries (CountryID)
)
GO

CREATE TABLE ManyToOne.Suppliers (
  SupplierID int IDENTITY,
  CompanyName nvarchar(40) COLLATE Cyrillic_General_CI_AS NOT NULL,
  ContactName nvarchar(30) COLLATE Cyrillic_General_CI_AS NULL,
  ContactTitle nvarchar(30) COLLATE Cyrillic_General_CI_AS NULL,
  Address nvarchar(60) COLLATE Cyrillic_General_CI_AS NULL,
  City nvarchar(15) COLLATE Cyrillic_General_CI_AS NULL,
  Region nvarchar(15) COLLATE Cyrillic_General_CI_AS NULL,
  PostalCode nvarchar(10) COLLATE Cyrillic_General_CI_AS NULL,
  CountryID int NULL,
  Phone nvarchar(24) COLLATE Cyrillic_General_CI_AS NULL,
  Fax nvarchar(24) COLLATE Cyrillic_General_CI_AS NULL,
  HomePage ntext COLLATE Cyrillic_General_CI_AS NULL,
  CONSTRAINT PK_Suppliers PRIMARY KEY (SupplierID),
  CONSTRAINT FK_Suppliers_Countries FOREIGN KEY (CountryID) REFERENCES ManyToOne.Countries (CountryID)
)

The tables diagram is as follows:

Database Diagram

We perform the following sequence of operations:

  • create a NHibernate model;
  • add the Orders, Suppliers and Countries tables to the model.

Before defining a complex type, the model looks as follows:

Initial Model

We select the Address, City, Region, PostalCode properties and the Country navigation property of the Supplier entity which are used to describe address, drag and drop them into a separate complex type and call it ‘AddressType'; an association appears between this complex type and the Country class and from now on it is the AddressType complex type that contains the Country navigation property, and instead of the set of the moved fields the Supplier entity contains a property with the type equal to AddressType, let’s call it Address.

The association between AddressType and the Country entity looks as follows:

Association Editor window for a many-to-one association

As can be seen from the Association editor dialog box, part of relation settings is in the association itself, for the many-to-one case there is also a default mapping for the foreign key column of an entity which will contain the property of the specified complex type, but if necessary, it can be changed to a custom one for a certain entity property. This will be shown in our example further in this article.

Since we created the complex type on the basis of the Supplier entity properties set, custom mapping of these properties was defined for this entity automatically and looks as follows:

Properties Custom Mapping

Now it is necessary to delete the association between the Order and Country entities, and also delete the ShipAddress, ShipCity, ShipRegion, ShipPostalCode properties of the Order entity. Instead of these properties set, describing the ship owner’s company address, it is necessary to add one property with the name ‘ShipAddress’ and the type equal to AddressType. As the column names of the address description and foreign columns in the Order entity do not match with the default mapping of the AddressType complex type properties and the default mapping of the association foreign column, it is necessary to set custom mapping for the Order entity’s ShipAddress property to these columns as displayed below:

ShipAddress property custom mapping

As a result we have the following model:

Resulting Model

The generated code for the model is as follows:

    /// <summary>
    /// There are no comments for AddressType in the schema.
    /// </summary>
    public partial class AddressType {
        private string _Address;
        private string _City;
        private string _Region;
        private string _PostalCode;
        private Country _Country;    
        #region Extensibility Method Definitions        
        partial void OnCreated();        
        #endregion
        public AddressType()
        {
            OnCreated();
        }    
        /// <summary>
        /// There are no comments for Address in the schema.
        /// </summary>
        public virtual string Address
        {
            get
            {
                return this._Address;
            }
            set
            {
                this._Address = value;
            }
        }    
        /// <summary>
        /// There are no comments for City in the schema.
        /// </summary>
        public virtual string City
        {
            get
            {
                return this._City;
            }
            set
            {
                this._City = value;
            }
        }    
        /// <summary>
        /// There are no comments for Region in the schema.
        /// </summary>
        public virtual string Region
        {
            get
            {
                return this._Region;
            }
            set
            {
                this._Region = value;
            }
        }    
        /// <summary>
        /// There are no comments for PostalCode in the schema.
        /// </summary>
        public virtual string PostalCode
        {
            get
            {
                return this._PostalCode;
            }
            set
            {
                this._PostalCode = value;
            }
        }    
        /// <summary>
        /// There are no comments for Country in the schema.
        /// </summary>
        public virtual Country Country
        {
            get
            {
                return this._Country;
            }
            set
            {
                this._Country = value;
            }
        }
    }
    /// <summary>
    /// There are no comments for Country in the schema.
    /// </summary>
    public partial class Country {
        private int _CountryID;
        private string _CountryName;    
        #region Extensibility Method Definitions        
        partial void OnCreated();        
        #endregion
        public Country()
        {
            OnCreated();
        }    
        /// <summary>
        /// There are no comments for CountryID in the schema.
        /// </summary>
        public virtual int CountryID
        {
            get
            {
                return this._CountryID;
            }
            set
            {
                this._CountryID = value;
            }
        }    
        /// <summary>
        /// There are no comments for CountryName in the schema.
        /// </summary>
        public virtual string CountryName
        {
            get
            {
                return this._CountryName;
            }
            set
            {
                this._CountryName = value;
            }
        }
    }
    /// <summary>
    /// There are no comments for Supplier in the schema.
    /// </summary>
    public partial class Supplier {
        private int _SupplierID;
        private string _CompanyName;
        private string _ContactName;
        private string _ContactTitle;
        private string _Phone;
        private string _Fax;
        private string _HomePage;
        private AddressType _Address;    
        #region Extensibility Method Definitions        
        partial void OnCreated();        
        #endregion
        public Supplier()
        {
            this._Address = new AddressType();
            OnCreated();
        }    
        /// <summary>
        /// There are no comments for SupplierID in the schema.
        /// </summary>
        public virtual int SupplierID
        {
            get
            {
                return this._SupplierID;
            }
            set
            {
                this._SupplierID = value;
            }
        }    
        /// <summary>
        /// There are no comments for CompanyName in the schema.
        /// </summary>
        public virtual string CompanyName
        {
            get
            {
                return this._CompanyName;
            }
            set
            {
                this._CompanyName = value;
            }
        }    
        /// <summary>
        /// There are no comments for ContactName in the schema.
        /// </summary>
        public virtual string ContactName
        {
            get
            {
                return this._ContactName;
            }
            set
            {
                this._ContactName = value;
            }
        }    
        /// <summary>
        /// There are no comments for ContactTitle in the schema.
        /// </summary>
        public virtual string ContactTitle
        {
            get
            {
                return this._ContactTitle;
            }
            set
            {
                this._ContactTitle = value;
            }
        }    
        /// <summary>
        /// There are no comments for Phone in the schema.
        /// </summary>
        public virtual string Phone
        {
            get
            {
                return this._Phone;
            }
            set
            {
                this._Phone = value;
            }
        }    
        /// <summary>
        /// There are no comments for Fax in the schema.
        /// </summary>
        public virtual string Fax
        {
            get
            {
                return this._Fax;
            }
            set
            {
                this._Fax = value;
            }
        }    
        /// <summary>
        /// There are no comments for HomePage in the schema.
        /// </summary>
        public virtual string HomePage
        {
            get
            {
                return this._HomePage;
            }
            set
            {
                this._HomePage = value;
            }
        }    
        /// <summary>
        /// There are no comments for Address in the schema.
        /// </summary>
        public virtual AddressType Address
        {
            get
            {
                return this._Address;
            }
            set
            {
                this._Address = value;
            }
        }
    }
    /// <summary>
    /// There are no comments for Order in the schema.
    /// </summary>
    public partial class Order {
        private int _OrderID;
        private string _Customer;
        private string _Employee;
        private System.Nullable<System.DateTime> _OrderDate;
        private System.Nullable<System.DateTime> _RequiredDate;
        private System.Nullable<System.DateTime> _ShippedDate;
        private System.Nullable<decimal> _Freight;
        private string _ShipName;
        private AddressType _ShipAddress;    
        #region Extensibility Method Definitions        
        partial void OnCreated();        
        #endregion
        public Order()
        {
            this._ShipAddress = new AddressType();
            OnCreated();
        }    
        /// <summary>
        /// There are no comments for OrderID in the schema.
        /// </summary>
        public virtual int OrderID
        {
            get
            {
                return this._OrderID;
            }
            set
            {
                this._OrderID = value;
            }
        }    
        /// <summary>
        /// There are no comments for Customer in the schema.
        /// </summary>
        public virtual string Customer
        {
            get
            {
                return this._Customer;
            }
            set
            {
                this._Customer = value;
            }
        }    
        /// <summary>
        /// There are no comments for Employee in the schema.
        /// </summary>
        public virtual string Employee
        {
            get
            {
                return this._Employee;
            }
            set
            {
                this._Employee = value;
            }
        }    
        /// <summary>
        /// There are no comments for OrderDate in the schema.
        /// </summary>
        public virtual System.Nullable<System.DateTime> OrderDate
        {
            get
            {
                return this._OrderDate;
            }
            set
            {
                this._OrderDate = value;
            }
        }    
        /// <summary>
        /// There are no comments for RequiredDate in the schema.
        /// </summary>
        public virtual System.Nullable<System.DateTime> RequiredDate
        {
            get
            {
                return this._RequiredDate;
            }
            set
            {
                this._RequiredDate = value;
            }
        }    
        /// <summary>
        /// There are no comments for ShippedDate in the schema.
        /// </summary>
        public virtual System.Nullable<System.DateTime> ShippedDate
        {
            get
            {
                return this._ShippedDate;
            }
            set
            {
                this._ShippedDate = value;
            }
        }    
        /// <summary>
        /// There are no comments for Freight in the schema.
        /// </summary>
        public virtual System.Nullable<decimal> Freight
        {
            get
            {
                return this._Freight;
            }
            set
            {
                this._Freight = value;
            }
        }    
        /// <summary>
        /// There are no comments for ShipName in the schema.
        /// </summary>
        public virtual string ShipName
        {
            get
            {
                return this._ShipName;
            }
            set
            {
                this._ShipName = value;
            }
        }    
        /// <summary>
        /// There are no comments for ShipAddress in the schema.
        /// </summary>
        public virtual AddressType ShipAddress
        {
            get
            {
                return this._ShipAddress;
            }
            set
            {
                this._ShipAddress = value;
            }
        }
    }

As can be seen from the generated code, a property is generated in the AddressType class, containing a reference to the Country class, and the classes Order and Supplier in their turn both contain a property with type equal to AddressType, and these properties describe addresses of the shipping company and vendor respectively.

The xml-mapping generated for the model looks as follows:

<hibernate-mapping namespace="testmodellerModel" xmlns="urn:nhibernate-mapping-2.2">
  <class name="Supplier" table="Suppliers" schema="ManyToOne">
    <id name="SupplierID" type="Int32">
      <column name="SupplierID" not-null="true" precision="10" scale="0" sql-type="int" />
      <generator class="identity" />
    </id>
    <property name="CompanyName" type="String">
      <column name="CompanyName" not-null="true" length="40" sql-type="nvarchar" />
    </property>
    <property name="ContactName" type="String">
      <column name="ContactName" not-null="false" length="30" sql-type="nvarchar" />
    </property>
    <property name="ContactTitle" type="String">
      <column name="ContactTitle" not-null="false" length="30" sql-type="nvarchar" />
    </property>
    <property name="Phone" type="String">
      <column name="Phone" not-null="false" length="24" sql-type="nvarchar" />
    </property>
    <property name="Fax" type="String">
      <column name="Fax" not-null="false" length="24" sql-type="nvarchar" />
    </property>
    <property name="HomePage" type="StringClob">
      <column name="HomePage" not-null="false" length="1073741823" sql-type="ntext" />
    </property>
    <component name="Address" class="AddressType">
      <property name="Address" type="String">
        <column name="Address" not-null="false" length="60" sql-type="nvarchar" />
      </property>
      <property name="City" type="String">
        <column name="City" not-null="false" length="15" sql-type="nvarchar" />
      </property>
      <property name="Region" type="String">
        <column name="Region" not-null="false" length="15" sql-type="nvarchar" />
      </property>
      <property name="PostalCode" type="String">
        <column name="PostalCode" not-null="false" length="10" sql-type="nvarchar" />
      </property>
      <many-to-one name="Country" class="Country">
        <column name="CountryID" not-null="false" precision="10" scale="0" sql-type="int" />
      </many-to-one>
    </component>
  </class>
  <class name="Order" table="Orders" schema="ManyToOne">
    <id name="OrderID" type="Int32">
      <column name="OrderID" not-null="true" precision="10" scale="0" sql-type="int" />
      <generator class="identity" />
    </id>
    <property name="Customer" type="String">
      <column name="Customer" not-null="true" length="50" sql-type="nvarchar" />
    </property>
    <property name="Employee" type="String">
      <column name="Employee" not-null="true" length="50" sql-type="nvarchar" />
    </property>
    <property name="OrderDate" type="DateTime">
      <column name="OrderDate" not-null="false" sql-type="datetime" />
    </property>
    <property name="RequiredDate" type="DateTime">
      <column name="RequiredDate" not-null="false" sql-type="datetime" />
    </property>
    <property name="ShippedDate" type="DateTime">
      <column name="ShippedDate" not-null="false" sql-type="datetime" />
    </property>
    <property name="Freight" type="Decimal">
      <column name="Freight" default="0" not-null="false" precision="19" scale="4" sql-type="money" />
    </property>
    <property name="ShipName" type="String">
      <column name="ShipName" not-null="false" length="40" sql-type="nvarchar" />
    </property>
    <component name="ShipAddress" class="AddressType">
      <property name="Address" type="String">
        <column name="ShipAddress" not-null="false" length="60" sql-type="nvarchar" />
      </property>
      <property name="City" type="String">
        <column name="ShipCity" not-null="false" length="15" sql-type="nvarchar" />
      </property>
      <property name="Region" type="String">
        <column name="ShipRegion" not-null="false" length="15" sql-type="nvarchar" />
      </property>
      <property name="PostalCode" type="String">
        <column name="ShipPostalCode" not-null="false" length="10" sql-type="nvarchar" />
      </property>
      <many-to-one name="Country" class="Country">
        <column name="ShipCountryID" not-null="true" precision="10" scale="0" sql-type="int" />
      </many-to-one>
    </component>
  </class>
  <class name="Country" table="Countries" schema="ManyToOne">
    <id name="CountryID" type="Int32">
      <column name="CountryID" not-null="true" precision="10" scale="0" sql-type="int" />
      <generator class="assigned" />
    </id>
    <property name="CountryName" type="String">
      <column name="CountryName" not-null="true" length="15" sql-type="nvarchar" />
    </property>
  </class>
</hibernate-mapping>

The NHibernate model for Entity Developer created in this example can be downloaded here

Comments are closed.