Table Splitting

This is an ORM initiative that allows a single table to be splited into more than one Entities . Sometimes, in our development world, we tend to work on databases that are not well normalized. We cannot bring this form of designs into our fine grained object oriented designs. For a quick example, let us examine the table schema of the good old customer table from northwind. After our examination, i will ask you guys to tell me which fields needs to be in a separate table, and based on common sense, we shall de-normalize this table from the entity point and not the table because our DBA thinks he has done a good job. DBA's forgive me, but sometimes we must understand that we think in objects over here.

So lets examine our good old customer table from Northwind
customer-northwind.PNG
Looking at the customer table above, here are the fields that i thought we will move to a different entity.
  • Address
  • City
  • Region
  • PostCode
  • Country

The above columns, will be mapped into a different entity called CustomerAddress

Since our DBA's does not want to normalize this table from the database front, we have to do it from the Entity aspect. The following code are how we have splitted the table into two entities.
Customer Mapping
    [Entity("Customers")]
    public partial class Customer
    {
        [Key( "CustomerID" , AutoKey = false )]
        public virtual string CustomerID { get; set; }

        .... other mappings

        [OneToOne( IsSplit = true )]
        public virtual CustomerAddress Address { get; set; }
    }

Note the IsSplit property of the OneToOne attribute on the Address property. This signifies that the OneToOne relationship is defined based on splitting. we shall now proceed to mapping our two entities

CustomerAddress Mapping (The Entity Split)
    [Entity("Customers")]
    public partial class CustomerAddress
    {
        [Key( "CustomerID" , AutoKey = false )]
        public virtual string CustomerID { get; set; }

        [Field( "Address" )]
        public virtual string Address { get; set; }

        [Field( "City" )]
        public virtual string City { get; set; }

        [Field( "Region" )]
        public virtual string Region { get; set; }

        [Field( "PostalCode" )]
        public virtual string PostalCode { get; set; }

        [Field( "Country" )]
        public virtual string Country { get; set; }
    }


With this mapping, we have successfully derived two entities from a single table. The following example is a simple query and generated SQL against the Entity Splitting strategy.

            using ( EntityManager manager = new EntityManager( ) )
            {
                manager.OpenDatabaseSession( );

                var customers = from customer in manager.AsQueryable<Customer>()
                                select new
                                {
                                    Address = customer.Address.Address,
                                    City = customer.Address.City,
                                    ContactName = customer.ContactName,
                                };

                foreach( var customer in customers )
                {
                    Console.WriteLine( "{0} {1} {2}" ,
                                            customer.Address ,
                                                customer.City ,
                                                    customer.ContactName );
                }
}

Generated SQL
SELECT  
      [t1].[Address] AS [R0] , 
      [t1].[City] AS [R1] , 
      [t2].[ContactName] AS [R2] 
FROM  [Customers] [t2], 
      ( SELECT  
                [t0].[Address] AS [Address],
                [t0].[CustomerID] AS [CustomerID],
                [t0].[City] AS [City] 
        FROM  [Customers] [t0] 
      ) t1  
WHERE  [t2].[CustomerID]  =  [t1].[CustomerID]

Last edited Feb 8, 2010 at 10:17 AM by ahmedsalako, version 3

Comments

No comments yet.