Converting SQL to LINQ, Part 4: Functions (Bill Horst)

This post assumes you’ve read the previous posts in this series:

This post will discuss scalar and aggregate functions.

Functions

SQL SELECT clauses often involve functions, which can be scalar or aggregate. An aggregate function is applied to a field over all the selected records, while a scalar function is called with individual values, one record at a time. It is possible to re-create both kinds of functions with VB LINQ expressions, but in very different ways.

Scalar Functions

Scalar functions are called on each record with whatever parameters are specified. They can appear various places in a SQL query, such as in the SELECT clause. The Scalar Functions available differ from system to system, but usually, there will be an analogous VB method that can be used. If using a Scalar Function in a LINQ Select clause, you will probably need to specify an alias as well (FirstThreeLetters, CurrentTime).

SQL

SELECT LEFT(ItemName, 3) FirstThreeLetters, NOW() CurrentTime

FROM OrderTable

From Shipment In OrderTable _

Select FirstThreeLetters = Left(Shipment.ItemName, 3), CurrentTime = Now

In the above case, Left and Now are methods already built into VB, defined in Microsoft.VisualBasic.dll. This will likely be the case for most common scalar functions you will find in SQL statements. Even if the function you wish to call does not exist in VB already, you can define your own methods, too. However, user-defined methods cannot be used in a Database query, as they will throw an exception at runtime. In the below example, MyFunction is a user-defined function called from the Select clause.

From Shipment In OrderTable _

Select MyFunction(Shipment.Cost, Shipment.ShippingZip)

Aggregate Functions

Aggregate functions are called on certain fields over an entire set of records, and return one value. In a SQL statement, they can appear in the SELECT clause. With VB LINQ, this concept appears a bit differently.

A VB LINQ expression usually begins with a From clause. However, they can also begin with an Aggregate clause. The Aggregate clause has the same syntax as a From clause, except that it starts with a different keyword. If a query starts with an Aggregate clause, it must end with an Into clause. An Into clause is a comma-delimited list of Aggregate function calls, with aliases that can accompany them. The below example shows a SQL SELECT statement that uses Aggregate functions, and an equivalent VB LINQ expression.

SQL

SELECT SUM(Cost) TotalCost, COUNT(*)

FROM OrderTable

WHERE OrderDate > “Sept-29-2007”

Aggregate Shipment In OrderTable _

Where Shipment.OrderDate > #9/29/2007# _

Into TotalCost = Sum(Shipment.Cost), Count()

Next week, I will discuss GROUP BY and HAVING.