We're busy working on EntitySpaces 2008 (ES2008) and thought we would demonstrate to you how subqueries will work. If you are familiar with our Join syntax then subqueries will feel like a perfect extension. The EntitySpaces 2008 architecture allows subqueries on the Select, the Where, the From, and the Join statement. Before we look at subqueries take a moment to review how our join syntax works. The EntitySpaces DynamicQuery API has proven to be very popular, so much so that other architectures are following suit.

EntitySpaces Join Syntax Review

Notice that we create three query objects and then relate them through InnerJoins and finally ask the CustomerCollection to load the query.

CustomerQuery cust = new CustomerQuery ("c");
OrderQuery order = new OrderQuery ("o");
OrderItemQuery item = new OrderItemQuery ("oi");

cust.Select(cust.CustomerName, (item.Quantity * item.UnitPrice).Sum().As("TotalSales"));
cust.InnerJoin(order).On(order.CustID == cust.CustomerID);
cust.InnerJoin(item).On(item.OrderID == order.OrderID);
cust.GroupBy(cust.CustomerName);
cust.OrderBy("TotalSales", esOrderByDirection.Descending);

CustomerCollection coll = new CustomerCollection ();
coll.Load(cust);

Pretty sweet and that is supported in ES2007 which is shipping now. But we're just getting started. Now let's look at some subquery samples.

EntitySpaces SubQuery in the Select Statement

This subquery determines the Maximum Unit Price for each order in the system.

OrdersQuery orders = new OrdersQuery("orders");
OrderDetailsQuery details = new OrderDetailsQuery("details");

orders.Select
(
    orders.OrderID,
    orders.OrderDate,
    details.Select(details.UnitPrice.Max()).Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
OrdersCollection coll = new OrdersCollection();
coll.Load(orders);

The resulting SQL generated by our SqlClientProvider is as follows.

SELECT orders.[OrderID],orders.[OrderDate],
(
    SELECT MAX(details.[UnitPrice]) AS 'UnitPrice' 
    FROM [Northwind].[dbo].[Order Details] details
    WHERE orders.[OrderID] = details.[OrderID]
) AS MaxUnitPrice 
FROM [Northwind].[dbo].[Orders] orders

The resulting data is as follows:

OrderID     OrderDate               MaxUnitPrice
----------- ----------------------- --------------
10248       1996-07-04 00:00:00.000    34.80
10249       1996-07-05 00:00:00.000    42.40
10250       1996-07-08 00:00:00.000    42.40
10251       1996-07-08 00:00:00.000    16.80
10252       1996-07-09 00:00:00.000    64.80
10253       1996-07-10 00:00:00.000    16.00
10254       1996-07-11 00:00:00.000    19.20
10255       1996-07-12 00:00:00.000    44.00
10256       1996-07-15 00:00:00.000    26.20

EntitySpaces SubQuery in the From Clause

Now let's look at using a subquery used in the From clause which returns the average freight per company.

OrdersQuery o = new OrdersQuery("o");

CustomersQuery c = new CustomersQuery("c");
c.Select(c.CompanyName, c.Country, o.Freight);
c.From
    (
        o.Select(o.CustomerID, o.Freight.Avg()).GroupBy(o.CustomerID)
    ).As("s");
c.InnerJoin(c).On(c.CustomerID == o.CustomerID);

CustomersCollection coll = new CustomersCollection();
coll.Load(c);

The resulting SQL generated by our SqlClientProvider is as follows.

SELECT c.[CompanyName],c.[Country],s.[Freight] 
FROM
(
    SELECT o.[CustomerID],AVG(o.[Freight]) AS 'Freight' 
    FROM [Northwind].[dbo].[Orders] o
    GROUP BY o.[CustomerID]
) AS s
INNER JOIN [Northwind].[dbo].[Customers] c
ON c.[CustomerID] = s.[CustomerID]

The resulting rows look like this:

CompanyName                              Country         Freight
---------------------------------------- --------------- -------
Alfreds Futterkiste                      Germany         37.5966
Ana Trujillo Emparedados y helados       Mexico          24.355
Antonio Moreno Taquería                  Mexico          38.36
Around the Horn                          UK              36.3038
Berglunds snabbköp                       Sweden          86.64
Blauer See Delikatessen                  Germany         24.0371
Blondesddsl père et fils                 France          56.6963
Bólido Comidas preparadas                Spain           63.7233
Bon app'                                 France          79.8747
Bottom-Dollar Markets                    Canada          56.7107

EntitySpaces SubQuery in the Where Clause

Here we have returned a list of countries where customers live where there is no supplier located in that country. Here we pass a subquery to the NotIn() operator.

SuppliersQuery supp = new SuppliersQuery("supp");
supp.es.Distinct = true;
supp.Select(supp.Country);

CustomersQuery cust = new CustomersQuery("cust");
cust.es.Distinct = true;
cust.Select(cust.Country);
cust.Where(cust.Country.NotIn(supp));

CustomersCollection coll = new CustomersCollection();
coll.Load(cust);

The resulting SQL generated by our SqlClientProvider is as follows:

SELECT DISTINCT cust.[Country] 
FROM [Northwind].[dbo].[Customers] cust
WHERE cust.[Country] NOT IN
(
    SELECT DISTINCT supp.[Country] 
    FROM [Northwind].[dbo].[Suppliers] supp
)

The resulting rows look like this:

Country
---------------
Argentina
Austria
Belgium
Ireland
Mexico
Poland
Portugal
Switzerland
Venezuela

Although not shown here it is also possible to use a subquery in the Join statement. Of course, subqueries can be nested in various ways too. We hope this gives you a glimpse of what is coming in ES2008. We are making great progress. The beauty of course is this will work on all of the databases we support.

From mobile devices to large scale enterprise solutions in need of serious transaction support, EntitySpaces can meet your needs. Whether you’re writing an ASP.NET application with medium trust requirements, or a Windows.Forms application, the EntitySpaces architecture is there for you. EntitySpaces is provider independent, which means that you can run the same binary code against any of the supported databases. EntitySpaces is available in both C# and VB.NET. EntitySpaces uses no reflection, no XML files, and sports a tiny foot print of less than 200k. Pound for pound, EntitySpaces is one tough, dependable .NET architecture.

The EntitySpaces Team
--

EntitySpaces LLC
Persistence Layer and Business Objects for Microsoft .NET
http://www.entityspaces.net