This project is read-only.
Here is an example of join using Data Broker. It is designed to be simple; it does not use a fluent style simply because it is too limiting - with the nested constructor we can define virtually any combination of entities. Their support is defined by the entities themselves so depending on the order of the constructors we may have INNER or LEFT joins.


        [Test]        
        public void TestJoin()
        {
            var sc = new Filter { MaxRows = 5000 };
            var customers = D2Bk.Fetch(sc, new Customers(new Orders(new Order_details())));
            Assert.AreEqual(2157, customers.Count);
        }


Here's the resultting query generated by Data Broker:

SELECT [Order_details].Orderid AS Order_detailsOrderid, [Order_details].Productid AS Order_detailsProductid, [Order_details].Unitprice AS Order_detailsUnitprice, 
[Order_details].Quantity AS Order_detailsQuantity, [Order_details].Discount AS Order_detailsDiscount, [Orders].Orderid AS OrdersOrderid, [Orders].Customerid AS OrdersCustomerid, 
[Orders].Employeeid AS OrdersEmployeeid, [Orders].Orderdate AS OrdersOrderdate, [Orders].Requireddate AS OrdersRequireddate, [Orders].Shippeddate AS OrdersShippeddate, 
[Orders].Shipvia AS OrdersShipvia, [Orders].Freight AS OrdersFreight, [Orders].Shipname AS OrdersShipname, [Orders].Shipaddress AS OrdersShipaddress, 
[Orders].Shipcity AS OrdersShipcity, [Orders].Shipregion AS OrdersShipregion, [Orders].Shippostalcode AS OrdersShippostalcode, [Orders].Shipcountry AS OrdersShipcountry, 
[Customers].Customerid AS CustomersCustomerid, [Customers].Companyname AS CustomersCompanyname, [Customers].Contactname AS CustomersContactname, 
[Customers].Contacttitle AS CustomersContacttitle, [Customers].Address AS CustomersAddress, [Customers].City AS CustomersCity, [Customers].Region AS CustomersRegion, 
[Customers].Postalcode AS CustomersPostalcode, [Customers].Country AS CustomersCountry, [Customers].Phone AS CustomersPhone, [Customers].Fax AS CustomersFax 
FROM [Customers]  [Customers]  
  LEFT OUTER JOIN  [Orders]  [Orders]  ON [Orders].Customerid = [Customers].Customerid
  LEFT OUTER JOIN  [Order details]  [Order_details]  ON [Order_details].Orderid = [Orders].Orderid



Now, if we invert the order of the constructors here's what we get:
        [Test]
        public void TestJoinInner()
        {
            var sc = new Filter { MaxRows = 5000 };
            var customers = D2Bk.Fetch(sc, new Order_details(new Orders(new Customers ())));
            Assert.AreEqual(2155, customers.Count);
        }


SELECT [Customers].Customerid AS CustomersCustomerid, [Customers].Companyname AS CustomersCompanyname, [Customers].Contactname AS CustomersContactname, 
[Customers].Contacttitle AS CustomersContacttitle, [Customers].Address AS CustomersAddress, [Customers].City AS CustomersCity, [Customers].Region AS CustomersRegion, 
[Customers].Postalcode AS CustomersPostalcode, [Customers].Country AS CustomersCountry, [Customers].Phone AS CustomersPhone, [Customers].Fax AS CustomersFax, 
[Orders].Orderid AS OrdersOrderid, [Orders].Customerid AS OrdersCustomerid, [Orders].Employeeid AS OrdersEmployeeid, [Orders].Orderdate AS OrdersOrderdate, 
[Orders].Requireddate AS OrdersRequireddate, [Orders].Shippeddate AS OrdersShippeddate, [Orders].Shipvia AS OrdersShipvia, [Orders].Freight AS OrdersFreight, 
[Orders].Shipname AS OrdersShipname, [Orders].Shipaddress AS OrdersShipaddress, [Orders].Shipcity AS OrdersShipcity, [Orders].Shipregion AS OrdersShipregion, 
[Orders].Shippostalcode AS OrdersShippostalcode, [Orders].Shipcountry AS OrdersShipcountry, [Order_details].Orderid AS Order_detailsOrderid, 
[Order_details].Productid AS Order_detailsProductid, [Order_details].Unitprice AS Order_detailsUnitprice, [Order_details].Quantity AS Order_detailsQuantity, 
[Order_details].Discount AS Order_detailsDiscount 
FROM [Order details]  [Order_details]  
  INNER JOIN  [Orders]  [Orders]  ON [Orders].Orderid = [Order_details].Orderid
  INNER JOIN  [Customers]  [Customers]  ON [Customers].Customerid = [Orders].Customerid

Last edited Mar 3, 2009 at 12:13 AM by dbroker, version 5

Comments

No comments yet.