Use Server Functions in NDO

Many SQL databases support server functions. The function calls can be used anywhere in a query where a scalar value can be used:

SELECT * FROM Employee WHERE CONCAT(firstName, lastName) = 'Mirko Matytschak';

In Sql Server, CONCAT returns an NVarChar value, which can then be used like a string in the query.

The areas of application of Functions are mostly database-specific, but there are also functions that work on several databases with the same code. The JSON functions are a good example of this. The most important JSON functions such as JSON_QUERY and JSON_VALUE work the same on Sql Server and on MariaDb (= MySql).

Such functionality often cannot be reproduced by client-side code, which is why we have decided to support them in NDO from version 4.0.6 on. In NDOql this is pretty simple:

var q = new NDOQuery<Employee>(pm, "CONCAT (firstName, lastName) = {0}";
q.Parameters.Add("Mirko Matytschak");
var result = q.Execute();

The NDOql parser recognizes functions by their syntax and forwards them unchanged into the SQL code. The function parameters are of course viewed as identifiers by NDO and converted into column references accordingly.

Server Functions with Linq

Linq expressions are checked by the compiler. This means that the server functions must somehow be declared as C # code. This is done in the simplest possible way as static functions of a class:

class ServerFunctions
{
    public static string CONCAT(string s1, string s2)
    {
        return null;
    }
}

The implementation of the function does not matter, because the code is not executed, but the function call expression is translated into SQL code. The query then looks like this in Linq:

var result = pm.Objects<Employee>().Where(e => ServerFunctions.CONCAT(e.firstName, e.lastName) == "Mirko Matytschak").ResultTable;

or accordingly:

var result = from e in pm.Objects<Employee>() where ServerFunctions.CONCAT(e.firstName, e.lastName) == "Mirko Matytschak" select e;

Alias Names

SQL and NDOql are typeless, so it doesn't matter which result types are returned by the functions. So the following SQL expressions are correct:

SELECT * FROM Employee WHERE JSON_VALUE(json, '$.intValue') = 42;
SELECT * FROM Employee WHERE JSON_VALUE(json, '$.stringValue') = 'Hi there!';

if the following expression is in the json column:

{ intValue: 42, stringValue: "Hi there!"}

If you want to do this with Linq in NDO, the problem arises that the CONCAT method can only be programmed with one certain return value type. You can now define two classes, StringServerFunctions and IntServerFunctions both containing a JSON_VALUE method, but you will probably agree with my view that this is not very elegant. The solution is an attribute that specifies the name of the function, while the name of the method can be anything:

class ServerFunctions
{
    [ServerFunction("JSON_VALUE")]
    public static string JsonValueAsString(string json, string path)
    {
        return null;
    }
    [ServerFunction("JSON_VALUE")]
    public static int JsonValueAsInt(string json, string path)
    {
        return 0;
    }
}

Now the queries can be formulated accordingly:

var result = pm.Objects<Employee>().Where(e => ServerFunctions.JsonValueAsString(e.json, "$.intValue") == "Hi There!").ResultTable;
var result = pm.Objects<Employee>().Where(e => ServerFunctions.JsonValueAsInt(e.json, "$.intValue") == 42).ResultTable;

Data Synchronization between Sql Server and IndexedDb

The following examples apply only to Sql Server.

All modern browsers have a client-side database, the IndexedDb. This allows data to be fetched from the server and cached so that the data is retained on the client system between sessions. They don't have to be loaded from the server every time, which takes some of the load off the server. But how can you load data sets from the server that have changed in the meantime? Sql Server provides RowVersion columns for this. A RowVersion value is actually a long integer, but Sql Server stores it as an array with 8 bytes. If you want to convert a long value into a byte array, you can proceed as follows in C #:

ulong version = …
byte[] versionBytes = BitConverter.GetBytes( version ).Reverse().ToArray();

The byte order has to be reversed because .NET works with little-endian integers.

Such byte arrays can now be passed as parameters to the Sql Server, whereby the comparison operators work in the same way as with scalar values. The conversion of ulong to byte[] and vice versa enables you to work with ulong values in your application‘s context, and, for example, can be passed on as hex strings by REST services and converted into BigInt objects in JavaScript.

Now you can save the cached data records and an extra data record that only contains the row version in the IndexedDb. To query the latest data records, however, something else has to be taken into account: you want to query the data records only from transactions that have already been completed. The maximum row version that you can query for this is determined with the MIN_ACTIVE_ROWVERSION function. You can now declare the following method for the ServerFunction:

The query for the latest records could then look like this:

public static IEnumerable<Post> GetNewerThan( Guid userGuid, ulong version )
{
    byte[] versionBytes = BitConverter.GetBytes( version ).Reverse().ToArray();
    PersistenceManager pm = new PersistenceManager();
    return pm.Objects<Post>().Where(
        u => u.UserGuid == userGuid
        && u.rowVersion.GreaterThan( versionBytes )
        && u.rowVersion.LowerThan( SqlServerFunctions.MinActiveRowversion() )
    ).ResultTable;
}

With this query, you only get those objects whose row version is greater than the specified version.

You can see that the GreaterThan and LowerThan methods must be used here in Linq because the < and > operators are not defined for byte arrays in C #. The same applies to strings, by the way. These comparison methods are defined in the NDO.Linq namespace.

Without Server Functions, we would have no chance of getting correct results, since the query has no upper limit and therefore takes rowversion values into account that belong to transactions that have not yet been completed. Why this matter is so complicated in Sql Server is beyond the scope of this article. But the fact that you can make a reliable comparison of datasets in this way is what sets Sql Server apart from other databases. I looked in vain for such a functionality in MariaDb.

If you now ask yourself why we don't just use integer IDs, since the new data records have higher IDs than the old data records: You want to know when a data record has changed. Changed data records have a new RowVersion after they have been saved. The ID of the data record remains the same, the RowVersion becomes higher.

Read-Only Fields

Because the Rowversion columns cannot be written by the client, we have to tell NDO that these columns are exclusively written by the server. From the perspective of NDO, these columns are read-only. This is not to be confused with the C# readonly modifier. The variables that contain the RowVersion values must be writable, otherwise the values cannot be fetched from the server. You must therefore set the NDOReadOnly attribute (since NDO 4.0.7) for all fields whose values are generated by the server:

[NDOReadOnly]
byte[] rowVersion = null;  // Initialize with null, otherwise the compiler complains, that nobody writes to this variable

These fields are not taken into account by NDO when inserting and updating records.

But that's not all that should be considered. By default, the C# type byte[] is mapped to the database type Image. You can change this with the column attribute:

[Column( DbType = "rowversion" )]
[NDOReadOnly]
byte[] rowVersion = null;

This generates the correct DDL code with which you can create the tables in Sql Server, and the correct parameter type is used when transferring the RowVersion values for queries.

The complete Code

The complete solution for a class that uses row versions looks like this: