In general, Subselect is similar to View, except that the View is created on the database side, and some DBMSs do not support this functionality, while Subselect is created on the model side.
The following example illustrates how a subselect query can be created and used in Entity Developer for NHibernate.
Firstly, we create a class (for details on how to create a class, refer to Creating and Editing a Class section), add several properties with the required NHibernate type values to it, and via the Column property specify mapping to the corresponding column names of the database tables that will be queried in our subselect:
Then, we call the Properties window of the class, locate the Subselect property in it, click the drop-down list button next to the property and enter the following query into the displayed text box:
SELECT Orders.ShipName,
Orders.ShipCountry + ';' + Orders.ShipCity + ';' +
Orders.ShipAddress As ShipAddress,
Customers.CompanyName AS CustomerName,
Customers.Country + ';' + Customers.City + ';' + Customers.Address As CustomerAddress,
(FirstName + ' ' + LastName) AS SalesPerson,
Orders.OrderDate,
Orders.RequiredDate,
Orders.ShippedDate,
Shippers.CompanyName As ShipperName,
Products.ProductName,
"Order Details".UnitPrice,
"Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,
("Order Details".UnitPrice * Quantity * (1 - Discount) / 100)) * 100) AS ExtendedPrice,
Orders.Freight
FROM Shippers
INNER JOIN(Products
INNER JOIN((Employees
INNER JOIN(Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details"
ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia
At the next step we locate the Synchronize Tables property in the Properties window and declare the database tables indicated in the subselect to synchronize the created class with. This will ensure that queries against the class will not return stale data.
This completes the procedure of the Subselect query creation, but we will perform some actions more to make sure that the query and mapping specified are correct.
So, the following step is to execute the subselect query. For this, right-click the class and select Retrieve Data in the context menu. In case query execution fails, an error message will be displayed, otherwise, the Data grid containing the result of query execution will be returned:
To view the text of the query, right-click the SQL tab of the window. For more information on queries, see Working with Queries and Data.