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