Invoking SQL Functions

Several SQL functions are built into LightSpeed and can be used in queries.  If you are using LINQ, you need only specify the corresponding CLR method and LightSpeed will translate it to SQL.  You can also extend this mapping to custom functions.  If you are using query objects, you can specify some functions using query expression member functions, but in other cases must emit the SQL function explicitly.

Mapping SQL Functions in LINQ

When you write queries using LINQ, you write them using .NET objects.  To use a function in the query, you just write the appropriate .NET method or property in your query, and LightSpeed will translate it to the equivalent SQL function.  For example, if you want to perform a case‑insensitive comparison, you could use the .NET String.ToUpper method to force all your strings to upper case:

from e in unitOfWork.Employees
where e.LastName.ToUpper() == searchText.ToUpper()
select e;

LightSpeed translates this to the SQL UPPER function:

SELECT ... FROM Employee WHERE UPPER(Name) = @p0

Many standard .NET methods and properties are built into the LightSpeed provider.  However, your database may provide SQL functions that don’t have a .NET equivalent, or you may have created user-defined functions that you want to use in queries.

In this case, you can register a mapping between a .NET method and a SQL function.  Once this is done, you can use the .NET method in a query, and LightSpeed will translate it to the specified function.

To register a mapping between a .NET method and a SQL function, call ServerFunctionDescriptor.Register.  You can register a member method or an extension method: this allows you to create methods on existing classes purely to have something to map to SQL.  For example, suppose you wanted to call SQL Server’s (admittedly antiquated) DIFFERENCE function, which returns how similar two strings sound.  There is no String method that maps naturally to DIFFERENCE, but you can define and register an extension method:

// Declaring the extension method
public static class SoundexExtensions
{
  public static int SimilarityTo(this string first, string second)
  {
    throw new NotImplementedException();
  }
}
// Mapping the method
MethodInfo similarityTo = typeof(SoundexExtensions).GetMethod("SimilarityTo");
ServerFunctionDescriptor.Register(similarityTo, "DIFFERENCE");

Once a method is mapped, you can use it in a LINQ query just as if it were built into LightSpeed:

from t in UnitOfWork.Towns
orderby t.Name.SimilarityTo("radavleetsa") descending
select t;

The resulting SQL calls the SQL function to which the .NET method was mapped:

SELECT ... FROM Town ORDER BY DIFFERENCE(Name, @p0) DESC

Mapping to a Custom Function

The same technique applies to mapping .NET methods to user-defined functions.  However, you must be careful to specify the server-side function name in exactly the way the database wants to see it.  For example, SQL Server requires that user-defined function names be prefixed with the schema.  So when mapping your .NET method you must specify “dbo.MyFunction” instead of just “MyFunction.”

ServerFunctionDescriptor.Register(clrMethodInfo, "dbo.MyFunction");

Mapping Argument Order

When you map a .NET method to a SQL function, by default, the expression to which the .NET method is applied becomes the first argument to the SQL function.  Sometimes this is not appropriate.  For example, suppose you mapped the String.IndexOf method to the SQL Server CHARINDEX function.  CHARINDEX requires the string to be looked for as the first argument, not the string to look in.  ServerFunctionDescriptor.Register provides an overload which takes an implicit argument index.  If you use this, the expression to which the .NET method is applied – the ‘implicit’ argument – will appear at that (0‑based) index in the SQL function’s argument list.

ServerFunctionDescriptor.Register(indexOfMethod, "CHARINDEX", 1);

Mapping Member Functions

Microsoft SQL Server allows you to define custom .NET types and methods within the database.  A major use case for this is SQL Server 2008’s spatial data support, where the comparison functions are member functions of the geography and geometry data types.  Member methods are not called using the usual SQL syntax, and must therefore be mapped specially.  To indicate that the translation of a .NET method is a member method and must be emitted with member syntax, prefix the server method name with a dot.

MethodInfo method = typeof(SqlGeography).GetMethod("STDistance");
ServerFunctionDescriptor.Register(method, ".STDistance"); // note the dot

Invoking SQL Functions Using Query Objects

The mapping step in LINQ is needed because the C# and Visual Basic compiler type‑check LINQ expressions, so you can only call SQL functions by representing them as .NET methods on a suitable .NET type.  If you are using query objects, you can pass SQL function names as strings, avoiding the need for mapping.  To do this, use the Function method, passing the name of the SQL function and any required arguments:

unitOfWork.Find<Town>(
    Entity.Attribute("Name").Function("DIFFERENCE", "radavleetsa") > 2);

As with mapped functions in LINQ, the expression by default becomes the first argument, but you can override this by specifying an implicit argument index.  Also as with LINQ mappings, you can specify that the function should be called using member syntax by prefixing it with a dot.

Entity.Attribute("UserName").Function(1, "CHARINDEX", " ") > 0;
Entity.Attribute("Location").Function(".STDistance", searchLocation) < distance;