Now that EntitySpaces 2007.1.1119.0 has been released we thought we would start a series of blog posts that highlights some of API enhancements. The most obvious place to begin is the Dynamic Query API as that is what many of our customers work in every day. We start off with some simple queries and work up to more complex queries.
Query - Simple select with where clause
Here is a simple query that brings back only the FirstName column for only those records that begin with the letter "M". Notice we use the % sign to indicate any characters after. You also use the % signs for all other supported databases. The EntitySpaces data providers will adjust accordingly for all other databases. Also, notice that we are using the built in query object on the collection itself. We recommend that you use this syntax always when there is no join involved.
EmployeesCollection coll = new EmployeesCollection ();
coll.Query.Select(coll.Query.FirstName);
coll.Query.Where(coll.Query.FirstName.Like("M%");
if(coll.Query.Load())
{
// at least one record was loaded
}
The resulting SQL:
SELECT [FirstName] FROM [Northwind].[dbo].[Employees] WHERE [FirstName] LIKE @FirstName1
Query - Arithmetic operators and complex where clause with AND/OR
This query shows off some of the natural language features of the EntitySpaces Dynamic Query API. Notice the use of the + operator to concatenate the FullName and LastName and providing the new derived column with an alias. Also notice the Where clause and it's use the | operator. You can use & for AND and | for OR and you can use ( ) for precedence.
EmployeesCollection coll = new EmployeesCollection ();
coll.Query.Select((coll.Query.LastName + "," + coll.Query.FirstName).As("FullName"));
coll.Query.Where(coll.Query.City == "Indianapolis" | coll.Query.Region == "Northeast");
if(coll.Query.Load())
{
// at least one record was loaded
}
The resulting SQL:
SELECT ([LastName]+','+[FirstName]) AS 'FullName' FROM [Northwind].[dbo].[Employees] WHERE ([City] = @City1 OR [Region] = @Region2)
Query - Aggregates with OrderBy and GroupBy
This query shows off some of the natural language features of the EntitySpaces Dynamic Query API. Notice the use of the + operator to concatenate the FullName and LastName and providing the new derived column with an alias. Also notice the Where clause and it's use the | operator. You can use & for AND and | for OR and you can use ( ) for precedence. Also, we had to use two calls to OrderBy because of the dynamic column created by the alias, however, you can pass multiple columns in like this -> coll.Query.OrderBy(coll.Query.ProductID.Descending, coll.Query.CategoryID.Ascending)
OrderDetailsCollection coll = new OrderDetailsCollection ();
coll.Query.Select
(
coll.Query.ProductID,
(coll.Query.Quantity * coll.Query.UnitPrice).Sum().As("TotalPrice")
);
coll.Query.GroupBy(coll.Query.ProductID);
coll.Query.OrderBy(coll.Query.ProductID.Ascending);
coll.Query.OrderBy("TotalPrice", esOrderByDirection.Ascending);
if(coll.Query.Load())
{
// at least one record was loaded
}
The resulting SQL:
SELECT [ProductID],SUM(([Quantity]*[UnitPrice])) AS 'TotalPrice' FROM [Northwind].[dbo].[Order Details] GROUP BY [ProductID] ORDER BY [ProductID] ASC,[TotalPrice] ASC
Query - Join Syntax
Notice the syntax here is a little different. When creating join based queries you do not use the embedded query object on the collection, you create your own and be sure to give it an alias. When you are not creating a join use the embedded query object as the examples above show. Also, in join scenarios you can select all from a particular column by passing the query into the select statement as follows -> Select(cust, order.OrderId) in this example the select will be Customer.*, Order.OrderId.
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); // Notice we load the query via the Collection.Load() method
The resulting SQL:
SELECT c.[CustomerName],SUM(oi.[Quantity]) AS 'TotalSales'
FROM [ForeignKeyTest].[dbo].[Customer] c
JOIN [ForeignKeyTest].[dbo].[Order] o ON (o.[CustID] = c.[CustomerID])
JOIN [ForeignKeyTest].[dbo].[OrderItem] oi ON (oi.[OrderID] = o.[OrderID])
GROUP BY c.[CustomerName]
ORDER BY TotalSales DESC
Miscellaneous Tips and Tricks
- You can trick a query object and force it to load from a database view instead of the table as follows
query.es.QuerySource = "SomeView";
- Top, CountAll, Distinct, PageNumber, PageSize, LastQuery are all available in the coll.query.es data member.
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