Mindscape Blog » Blog Archive » Using SQL functions in LightSpeed

Using SQL functions in LightSpeed

LightSpeed 2 adds the ability to use SQL functions and arithmetic operators in your queries. We expect that most of the time, you’ll do this through LINQ, but if you’re targeting .NET 2.0 or you want to use a SQL function that doesn’t have a LINQ equivalent, it can be useful to know what’s going on behind the scenes.

For example, suppose that we want to retrieve a list of titles, but we want them all in lower case, perhaps as a canonical form for some sort of comparison. If we’re using LINQ, we can just call the .NET String.ToLower() function:

var query = from c in unitOfWork.Contributions
            select new { c.Id, c.Title.ToLower() };

Although this looks like a .NET call, LightSpeed translates it into a call to the SQL ==LOWER()== function and has it executed on the database.

If we’re not using LINQ, we can still get at the ==LOWER()== function, but it’s slightly harder work:

Query query = new Query(typeof(Contribution));
query.Projection.Add("Id");
query.Projection.Add(Entity.Attribute("Title").Function("LOWER"));
IDataReader results = unitOfWork.Project(query);

You can also use the .Function() syntax in query expressions to apply the function in SQL WHERE and ORDER BY clauses.

Arithmetic operators introduce an additional complexity: an arithmetic operator has two arguments. Consider an entity with UnitPrice and Quantity attributes. If we want to get the total price, namely UnitPrice times Quantity, we need to use the SQL * (multiplication) operator. Again, in LINQ, we can do this using our language’s multiplication operator, but if we want to do it without LINQ, it looks like this:

Query query = new Query(typeof(SalesLine));
query.Projection.Add("Id");
query.Projection.Add(Entity.Attribute("UnitPrice").Function(
  "*", Entity.Attribute("Quantity")));
IDataReader results = unitOfWork.Project(query);

Note the additional argument to the .Function() method. To call a SQL function with three or more arguments, such as REPLACE, just keep adding arguments.

You might notice that the * example uses the same syntax as the LOWER example even though * is an infix operator and LOWER is a function: that is, * goes between its arguments, as in UnitPrice * Quantity, whereas LOWER goes before its argument and needs brackets, as in ==LOWER(Title)==. LightSpeed knows which “functions” are actually infix operators and takes care of this for you.

(You might be wondering why we don’t support an infix notation in the LightSpeed query API? Why can’t you write Entity.Attribute(“UnitPrice”) * Entity.Attribute(“Quantity”)? The answer is that we expect that most people who need to do this will use LINQ. So optimising the experience of using infix operators in the query API hasn’t been a high priority.)

One last wrinkle. We can only apply functions to expressions. So far, we’ve always had an expression to go in as the first argument of the function. What happens if we want the expression to appear somewhere else in the argument list? For example, in PostgreSQL, if we want the year part of a date, we’d need to generate SQL such as DATE_PART(‘year’, DateOfBirth).

For this situation, you can supply an additional argument to .Function() specifying where in the argument list the expression should go:

Entity.Attribute("DateOfBirth").Function(1, "DATE_PART", "year")

Again, we’d generally recommend and expect that you use LINQ for this sort of thing — in this case using the .NET DateTime.Year property. This is not only easier to write and read, it also abstracts away the different function names and syntaxes between the different database engines.

Still, if ever you need the ability to get down to the raw SQL level, now you know how.