Many To Many mapping: Many instances of an entity to many instances of a related entity. In a relational world, Many To Many requires a join table. But in OOP we do not necessarily require the join object to be visible. This section will explain how to make use of the Many To Many relationship while leveraging rapid entity framework.

Following this analogy , a Customer can order many products, Many Products can be ordered by a customer. In a relational world, we will have the following schema designed for this scenario.

Download Sample here
Download the sql here

many-to-many.PNG
The next step is to derieve our mapping from the database model : The Rapid Entity Designer will be used for this demonstration.

entity-many-to-many.PNG

The Entities in the designer was dragged from the VS Server Explorer to the Rapid Designer Surface , the relationship given is One-ToMany and Many-To-One. The following further explains the relationship in the above diagram :
  • A Customer can order many Products
  • A Product can be ordered by many Customers

We would like to turn the two assertions above into the following :
  • Many Customers can Order Many Products
  • Many Products Can be Ordered by Many Customers

In other words, we would like to map our entities using the Many - To - Many approach. Now let us hand code the Many - To - Many mapping :

    public partial class Customer
    {

        .......  other Properties

        [ManyToMany( Type = typeof(Product ) , JoinTable = "CustomerOrders" ,
            OwnerColumn = "CustomerId" , JoinColumn = "ProductId" , Cascade = Cascade.ALL  )]
        public virtual IList<Product> Products
        {
            get;
            set;
        }
    }

The above mapping indicates that the Products property of the Customer class is to be viewed internally by Rapid Entity Framework as a property with Many-To-Many relationship. The following explains the Named Parameters of the attribute Many-To-Many :
  • Type : This is the type being fetched. In our situation, the Product class is the type being fetch.
  • JoinTable : This is the name of the join table, in our example, the CustomerOrders table name is the name of the join table that joins Customer to Product. Note* you do not necessarily have to create a class for CustomerOrders table, as REF will at runtime generate a memory class based on the table name.
  • OwnerColumn : The OwnerColumn is the foreign key column name in the JoinTable "CustomerOrders" which is mapped to a Primary Key in the Entity that owns the property. In our example, the OwnerColumn is CustomerId which is mapped to the CustomerId property of the Customer class. Which means join by Customer.CustomerId on CustomerOrders.CustomerId.
  • JoinColumn : This a foreign key column in the CustomerOrders table which is mapped to the Primary Key of the Entity being joined (In our example, that is the Product entity). Which means join by Product.ProductrId on CustomerOrders.Product.
  • Cascade : This is the action that will be taken by REF runtime engine upon relationships. In our example above, Cascade = Cascade.ALL means Create , Update , Delete operation is made on this relationship whenever it is made on the Owner Entity. In our example, when Customer entity is updated , the Many-To-Many relationship of the Product entity is also updated.

Using the Entities in Code
            string connection = @"Data Source=localhost;Initial Catalog=TestScenarios;Integrated Security=True";

            IConfiguration configuration = ConfigurationFactory.GetInstance( connection );
            configuration.ProviderDialect = ProviderDialect.SqlProvider;
            configuration.CacheSettings( 10 , 100 );

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

                Product product = new Product( );
                product.Price = 0.0;
                product.ProductName = "Rapid Entity Framework";

                Customer customer = new Customer( );
                customer.FirstName = "Mr Rapid";
                customer.LastName = "Yukon";
                customer.Products = new List<Product>( );
                customer.Products.Add( product );

                manager.CreateNewEntity<Customer>( ref customer );
            }

Querying the Many-To-Many relationship
  • (RQL)
                var t0 = manager.AsAlias<Customer>( );
                var results = new From( typeof( Customer ) ).As( t0 )
                                    .Where( t0.Products ).Count( m => m.GreaterThan( 0 ) )
                                    .SelectNew( ( ) => t0.LastName ).Execute( );
 SELECT  
	[t0].[LastName] AS [R0] 
FROM  [Customer] [t0]  
WHERE  ( SELECT  
		COUNT([sub2].[ProductId]) AS [ProductId] 
	 FROM  [Product] [sub2]  , 
	( SELECT  
		[sub0].[ProductId] AS [ProductId],
		[sub0].[CustomerId] AS [CustomerId] 
	  FROM  [CustomerOrders] [sub0]

	) sub1  
	WHERE  [sub2].[ProductId]  =  [sub1].[ProductId] 
	AND  ( [sub2].[ProductId]  =  [sub1].[ProductId] )   
	AND  ( [sub1].[CustomerId]  =  [t0].[CustomerId] ) 
)  >  @opt0_0_55166 

Querying the Many-To-Many relationship
  • (LINQ)
var linq = ( from cus in manager.AsQueryable<Customer>( )
                  where cus.Products.Count > 0
                    select cus.LastName
              ).ToList( );

Note* Both RQL and LINQ generates the same SQL query.

Last edited Feb 23, 2010 at 2:43 PM by ahmedsalako, version 26

Comments

No comments yet.