EntitySpaces, LLC will be delivering substantial SubQuery horsepower in its 2008 release. The EntitySpaces DynamicQuery API (just one of the features of using EntitySpaces) has proven to be a very powerful tool for .NET developers. Not only is the syntax very intuitive and similar to SQL itself but it is also database independent. Even more importantly, the EntitySpaces DynamicQuery API is fully supported on the Compact Framework, Mono, and in ASP.NET Medium Trust Mode. It is also supported for Microsoft SQL and SQL CE, Oracle, MySQL, PostgreSQL, VistaDB, and Microsoft Access and will run on the .NET 2.0 Framework and higher.
SelectAllExcept()
SelectAllExcept() is not really a SubQuery, just a convenient enhancement that allows you to select all except one or more listed columns.
EmployeeCollection coll = new EmployeeCollection();
coll.Query.SelectAllExcept(
coll.Query.EmployeeID,
coll.Query.FirstName);
coll.Query.Load();
Results:
SELECT [LastName],[Supervisor],[Age]
FROM [ForeignKeyTest].[dbo].[Employee]
Select SubQuery
A SubQuery in a Select clause must return a single value.
OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");
orders.Select
(
orders.OrderID,
orders.OrderDate,
details.Select(
details.UnitPrice.Max())
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
OrderCollection collection = new OrderCollection();
collection.Load(orders);
Results:
SELECT o.[OrderID],o.[OrderDate],
(SELECT MAX(oi.[UnitPrice]) AS 'UnitPrice'
FROM [ForeignKeyTest].[dbo].[OrderItem] oi
WHERE o.[OrderID] = oi.[OrderID]) AS MaxUnitPrice
FROM [ForeignKeyTest].[dbo].[Order] o
This is the same as the query above, but returns all columns in the Order table, instead of just OrderID and OrderDate. Notice that the Select clause contains orders, not orders.*. The SQL produced will use the supplied alias o.*.
OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");
orders.Select
(
orders,
details.Select(
details.UnitPrice.Max())
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
OrderCollection collection = new OrderCollection();
collection.Load(orders);
Results:
SELECT o.*,
(SELECT MAX(oi.[UnitPrice]) AS 'UnitPrice'
FROM [ForeignKeyTest].[dbo].[OrderItem] oi
WHERE o.[OrderID] = oi.[OrderID]) AS MaxUnitPrice
FROM [ForeignKeyTest].[dbo].[Order] o
From SubQuery
An aggregate requires a GROUP BY for each column in the SELECT that is not an aggregate. Sometimes you wish to include columns in your result set that you do not wish to group by. One way to accomplish this is by using a SubQuery in the From clause that contains the aggregate the way you want it grouped. The outer query contains the results of the aggregate, plus any additional columns.
If you use a SubQuery in a From clause, you must give the From clause its own alias (shown below as "sub"). In the outer query, to refer to an aliased element in the From SubQuery, use the inline raw SQL technique to qualify the aggregate's alias with the From clause alias, i.e., "<sub.OrderTotal>".
OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");
oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>");
oq.From
(
oiq.Select(oiq.OrderID,
(oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal"))
.GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[CustID],o.[OrderDate],sub.OrderTotal
FROM (
SELECT oi.[OrderID],
SUM((oi.[UnitPrice]*oi.[Quantity])) AS 'OrderTotal'
FROM [ForeignKeyTest].[dbo].[OrderItem] oi
GROUP BY oi.[OrderID]) AS sub
INNER JOIN [ForeignKeyTest].[dbo].[Order] o
ON o.[OrderID] = sub.[OrderID]
Where SubQuery
In and NotIn are two of the most common operators used in a Where SubQuery. The following produces a result set containing Territories that an Employee is not associated with.
// SubQuery of Territories that Employee 1 is assigned to.
EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
etq.Select(etq.TerrID);
etq.Where(etq.EmpID == 1);
// Territories that Employee 1 is not assigned to.
TerritoryQuery tq = new TerritoryQuery("t");
tq.Select(tq.Description);
tq.Where(tq.TerritoryID.NotIn(etq));
TerritoryCollection collection = new TerritoryCollection();
collection.Load(tq);
Results:
SELECT t.[Description]
FROM [ForeignKeyTest].[dbo].[Territory] t
WHERE t.[TerritoryID] NOT IN (
SELECT et.[TerrID]
FROM [ForeignKeyTest].[dbo].[EmployeeTerritory] et
WHERE et.[EmpID] = @EmpID1)
Exists evaluates to true, if the SubQuery returns a result set.
// SubQuery of Employees with a null Supervisor column.
EmployeeQuery sq = new EmployeeQuery("s");
sq.es.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.Supervisor.IsNull());
// If even one employee has a null supervisor,
// i.e., the above query has a result set,
// then run a list of all employees.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));
EmployeeCollection collection = new EmployeeCollection();
collection.Load(eq);
Results:
SELECT e.[EmployeeID],e.[Supervisor]
FROM [ForeignKeyTest].[dbo].[Employee] e
WHERE EXISTS (
SELECT DISTINCT s.[EmployeeID]
FROM [ForeignKeyTest].[dbo].[Employee] s
WHERE s.[Supervisor] IS NULL)
Join(query).On(SubQuery)
SubQueries cannot be used directly within a Join(SubQuery) clause, but they can be used within a Join(query).On(SubQuery) clause.
// Query for the Join
OrderItemQuery oiq = new OrderItemQuery("oi");
// SubQuery of OrderItems with a discount
OrderItemQuery oisq = new OrderItemQuery("ois");
oisq.es.Distinct = true;
oisq.Select(oisq.Discount);
oisq.Where(oisq.Discount > 0);
// Orders with discounted items
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oiq.Discount);
oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID &
oiq.Discount.In(oisq));
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[OrderID],oi.[Discount]
FROM [ForeignKeyTest].[dbo].[Order] o
INNER JOIN [ForeignKeyTest].[dbo].[OrderItem] oi
ON (o.[OrderID] = oi.[OrderID] AND oi.[Discount] IN (
SELECT DISTINCT ois.[Discount]
FROM [ForeignKeyTest].[dbo].[OrderItem] ois
WHERE ois.[Discount] > @Discount1))
Correlated SubQuery
A correlated SubQuery is where the inner query relies on an element of the outer query. The inner select cannot run on its own. Below, the inner pq query uses the outer query's oiq.ProductID in the Where() clause.
OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");
oiq.Select(
oiq.OrderID,
(oiq.Quantity * oiq.UnitPrice).Sum().As("Total")
);
oiq.Where(oiq.ProductID
.In(
pq.Select(pq.ProductID)
.Where(oiq.ProductID == pq.ProductID)
)
);
oiq.GroupBy(oiq.OrderID);
OrderItemCollection collection = new OrderItemCollection();
collection.Load(oiq);
Results:
SELECT oi.[OrderID],SUM((oi.[Quantity]*oi.[UnitPrice])) AS 'Total'
FROM [ForeignKeyTest].[dbo].[OrderItem] oi
WHERE oi.[ProductID] IN (
SELECT p.[ProductID]
FROM [ForeignKeyTest].[dbo].[Product] p
WHERE oi.[ProductID] = p.[ProductID])
GROUP BY oi.[OrderID]
Nested SubQuery
EntitySpaces supports nesting of SubQueries. Each database vendor has their own limits on just how deep the nesting can go. EntitySpaces supports two different syntax approaches to nested SubQueries.
Traditional SQL-style syntax is most useful if you already have a query designed using standard SQL, and are just converting it to a DynamicQuery.
OrderQuery oq = new OrderQuery("o");
CustomerQuery cq = new CustomerQuery("c");
EmployeeQuery eq = new EmployeeQuery("e");
// OrderID and CustID for customers who ordered on the same date
// a customer was added, and have a manager whose
// last name starts with 'S'.
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate
.In(
cq.Select(cq.DateAdded)
.Where(cq.Manager.In(
eq.Select(eq.EmployeeID)
.Where(eq.LastName.Like("S%"))
)
)
)
);
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[OrderID],o.[CustID]
FROM [ForeignKeyTest].[dbo].[Order] o
WHERE o.[OrderDate] IN (
SELECT c.[DateAdded]
FROM [ForeignKeyTest].[dbo].[Customer] c
WHERE c.[Manager] IN (
SELECT e.[EmployeeID]
FROM [ForeignKeyTest].[dbo].[Employee] e
WHERE e.[LastName] LIKE @LastName1) )
Nesting by query instance name can be easier to understand and construct, if you are starting from scratch, and have no pre-existing SQL to go by. The trick is to start with the inner-most SubQuery and work your way out. The query below produces the same results as the traditional SQL-style query above. The instance names are color coded to emphasize how they are nested.
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is in the
// CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate.In(cq));
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[OrderID],o.[CustID]
FROM [ForeignKeyTest].[dbo].[Order] o
WHERE o.[OrderDate] IN (
SELECT c.[DateAdded]
FROM [ForeignKeyTest].[dbo].[Customer] c
WHERE c.[Manager] IN (
SELECT e.[EmployeeID]
FROM [ForeignKeyTest].[dbo].[Employee] e
WHERE e.[LastName] LIKE @LastName1) )
Any, All, and Some
ANY, ALL, and SOME are SubQuery qualifiers. They precede the SubQuery they apply to. For most databases, ANY and SOME are synonymous. Usually, if you use an operator (>, >=, =, <, <=) in a Where clause against a SubQuery, then the SubQuery must return a single value. By applying a qualifier to the SubQuery, you can use operators against SubQueries that return multiple results.
Notice, below, that the ALL qualifier is set to true for the SubQuery with "cq.es.All = true;".
// DateAdded for Customers whose Manager = 3
CustomerQuery cq = new CustomerQuery("c");
cq.es.All = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager == 3);
// OrderID and CustID where the OrderDate is
// less than all of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate < cq);
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[OrderID],o.[CustID]
FROM [ForeignKeyTest].[dbo].[Order] o
WHERE o.[OrderDate] < ALL (
SELECT c.[DateAdded]
FROM [ForeignKeyTest].[dbo].[Customer] c
WHERE c.[Manager] = @Manager1)
Below, is a nested SubQuery. The ANY qualifier is set to true for the middle SubQuery with "cq.es.Any = true;".
// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));
// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.es.Any = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));
// OrderID and CustID where the OrderDate is
// less than any one of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(
oq.OrderID,
oq.CustID
);
oq.Where(oq.OrderDate < cq);
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Results:
SELECT o.[OrderID],o.[CustID]
FROM [ForeignKeyTest].[dbo].[Order] o
WHERE o.[OrderDate] < ANY (
SELECT c.[DateAdded]
FROM [ForeignKeyTest].[dbo].[Customer] c
WHERE c.[Manager] IN (
SELECT e.[EmployeeID]
FROM [ForeignKeyTest].[dbo].[Employee] e
WHERE e.[LastName] LIKE @LastName1) )
Some Final Thoughts
The examples given above were designed to demonstrate (and test) usage in a variety of settings. They are not necessarily the simplest, or most efficient, way to achieve the desired result set. Think of them as an API usage guide, not as design guidelines. Most SubQueries can be re-written as Joins, and most Joins can be re-written as SubQueries. If, while coding, you are having trouble conceptualizing one approach, then try the other.
Technically, a JOIN's ON clause can take almost any where_condition, and EntitySpaces supports many of these, including SubQueries. But, we agree with most authorities on the subject, that the ON clause should be reserved for the conditions that relate the two tables. All other conditional statements should be placed in a WHERE clause. Typically, the ON clause only contains a column from each table and a comparison operator.
For example, take the Join(query).On(SubQuery) example above. The much simpler query below returns the same result set. Its Where() clause not only simplifies the On() clause, but eliminates the SubQuery completely.
// Query for the Join
OrderItemQuery oiq = new OrderItemQuery("oi");
// Orders with discounted items
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oiq.Discount);
oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID);
oq.Where(oiq.Discount > 0);
OrderCollection collection = new OrderCollection();
collection.Load(oq);
Now might be a good time to point out that we are not in the complex query creation business. If you post a bunch of SQL, and ask, "How do I do this in EntitySpaces?", you may, or may not, get much of a response. The Peer to Peer forums are your best bet for that kind of post. On the other hand, if you post an EntitySpaces DynamicQuery that is throwing an exception, or producing inaccurate results, you will find us eager to resolve the issue.
We are going to need your help, however. As always, post the database and EntitySpaces version, the relevant code, the exception (if any), and the stack trace. We do not have your database schema, so if you can narrow down the issue using Northwind or pubs, that is a tremendous help. Posting obj.Query.es.LastQuery can provide valuable clues.
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, a Mono application, 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