CoolStorage implements an easy to use, yet powerful Object Query Language. Query expressions look a lot like standard SQL "WHERE" clauses, with the following important differences:
The following methods expect an (optional) query expression:
Query expressions are very similar to SQL WHERE expression, with one major difference: no column names should be used, but property names. It is possible to use relation properties in the query expression in a "dotted" notation. All required joins will be generated by CoolStorage, so you don't have to worry about it.
A dotted relation expression takes the following form:
*RelationProperty1.RelationProperty2.Property3*
As always, an example tells more than a thousand words, so let's use this expression as an example:
orders = Order.List("Customer.City = 'Brussels'");
The *Customer.City* expression will be translated to the "City" property of the related Customer record. The required joins will be generated by CoolStorage automatically. You can have as many "dots" as you like and combine many expressions in one query:
orders = Order.List("Customer.SalesContact.Name = 'Steve' and Customer.ZipCode = '2345'");
You are not limited to using many-to-one or one-to-one relations in your query expressions. You can use one-to-many relations or many-to-many relations as well, if used in a scalar expression like "sum" or "count" or "has".
For example:
orders = Order.List("has(OrderItems where Price>100) and sum(OrderItems.Price) > 1000");
Looks pretty intuitive, doesn't it? The query lists all orders which have at least one order item with a price higher than 100, and with a total price of more than 1000. No need to worry about joins. CoolStorage will take care of it.
Query expressions can take named parameters in the form "@ParameterName", even if the underlying database doesn't support named parameters. You can pass any primitive type (including DateTime values) as parameters.
There are 2 ways to pass parameters to a query. One way is to pass the parameter names and values directly as additional parameters to one of the methods expecting a query expression, like this:
orders = Order.List("Customer.SalesContact.Name = @Name and Customer.ZipCode=@ZipCode", "@Name", "Steve", "@ZipCode", "2345");
You can pass any number of parameters this way, as long as they are passed in pairs "name,value".
The other way of passing parameters is by building a CSParameterCollection and passing the collection in one of the query methods:
CSParameterCollection parameters = new CSParameterCollection(); parameters.Add("@Name", "Steve"); parameters.Add(new CSParameter("@ZipCode", "2345"); orders = Order.List("Customer.SalesContact.Name = @Name and Customer.ZipCode=@ZipCode", parameters);
It's even possible to pass objects as parameters and compare them to objects in your query expression, like this:
Customer customer = Customer.Read(customerId); SalesPerson me = SalesPerson.Read(myId); orders = Order.List("Customer=@Customer and SalesPerson=@SalesPerson", "@Customer", customer, "@SalesPerson", me);
In the exampe above, "Customer" and "SalesPerson" are both many-to-one relations of the Order class. CoolStorage allows you to compare them directly to objects passed as named parameters.
The advantages of using parameters are:
Scalar functions are functions which return a single value, and take any number of arguments. Most SQL scalar functions are supported in query expressions:
You can use these scalar functions in any query expression, for example:
orders = Order.List("LEN(Customer.Name) > 5");
Aggregate functions are functions that take collections (one-to-many or many-to-many collections) and which return a single value, usually a number (SUM,COUNT), or a true/false expression (HAS).
CoolStorage supports the following aggregate functions:
All aggregate functions are of the form "FUNCTION(Expression WHERE Condition)". The condition is optional. It is important to note that the condition acts on the collection on which the function acts. This means if "Expression" represents a list of order items, the WHERE expression should reference properties of the "OrderItem" class.
Below are a few examples on how to use these aggregate functions:
Order.List("COUNT(OrderItems) > 5"); // all orders with more than 5 order items Order.List("COUNT(OrderItems where Price>=100) > 0"); // all orders having an item which costs at least 100 Order.List("COUNTDISTINCT(OrderItems.ProductID) > 1"); // all orders having at least 2 different products Order.List("MAX(OrderItems.Price) > 500"); // all orders with at least one item more expensive than 500 Order.List("SUM(OrderItems.Price) < 1000"); // all orders with a price total of less than 1000 Customer.List("COUNT(Orders) >= 10"); // all customers having 10 or more orders
You can inject provider-specific keywords and functions in your CSList expression by prepending the name with a $ (dollar sign).
For example, if you're using SQL Server, you can find all people who's birthday is today like this:
var users = User.List("$DAY(DateOfBirth)=@Day AND $MONTH(DateOfBirth)=@Month", "@Month", DateTime.Now.Month, "@Day", DateTime.Now.Day);
Sort expressions are similar to query expressions, but they always take the form "property,property,property,...". It is similar to the ORDER BY clause in a SQL SELECT, with the following exceptions:
Examples:
// a list of orders sorted by customer name, and order date (reverse) Order.List().OrderedBy("Customer.Name,OrderDate-"); // a list of customers sorted by number of orders (reverse) Customer.List().OrderedBy("count(Orders)-");