Procedures

At the heart of Jerrycurl lie its procedures which are written with SQL and Razor code. You can think of them as client-side stored procedures with the simple definition that they take in a single input model, execute a collection of SQL statements and return instances of an output model.

A standard Razor procedure is created by placing a .cssql in the proper Commands or Queries subfolder and naming it so your accessor can find it.

The lookup mechanism is similar to that of ASP.NET between controllers and views and means that if you have a CustomersAccessor implementing a GetStats query method, it would (unless otherwise specified) look for a GetStats.cssql file in the Queries/Customers folder of your project.

Now, let's take a look at the structure of a Razor query. First the header, which is used to reference a subset of the object model as a set of projections.

1
2
3
4
5
-- Queries/Customers/GetStats.cssql
@result CustomerOrderStatsView
@model CustomerFilter
@project Customer c
@project Order o

The two main directives here are @model and @result which represent the input and output model and are directly related to the arguments specified by the accessor.

1
2
3
4
5
6
7
8
9
10
11
12
public class CustomersAccessor : Accessor
{
    public IList<CustomerStatsView> GetStats(DateTime minAge)
    {
        var filter = new CustomerFilter()
        {
            MinAge = minAge,
        };
        
        return this.Query<CustomerStatsView>(model: filter);
    }
}

Additionally you can declare any number of named projections, typically from the database model. In the example above we declare the c and o as projections of the Customer and Order tables, as they are the data sources for our query.

Now we can access these two tables through @c and @o in Razor, and we also have the predefined M and R properties that represent the @model and @result directives.

This gives us enough information to start writing the body of our .cssql file, which maps some customer data along with an order aggregate.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Queries/Customers/GetOrderStats.cssql
@result CustomerStatsView
@model CustomerFilter
@project Customer c
@project Order o

SELECT
    @c.Col(m => m.Name)        AS @R.Prop(m => m.CustomerName),
    @c.Col(m => m.Email)       AS @R.Prop(m => m.CustomerEmail),
    (
        SELECT  COUNT(*)
        FROM    @o.Tbl()
        WHERE   @o.Col(m => m.CustomerId) = @c.Col(m => m.Id)
    )                           AS @R.Prop(m => m.NumberOfOrders)
FROM
    @c.Tbl()
WHERE
    @c.Col(m => m.Created) >= @M.Par(m => m.MinAge)

A Razor procedure consists of plaintext SQL mixed with Razor blocks has a common pattern of a) navigation to a specific property with a lambda expression, and b) a call to an extension method writing SQL to the underlying buffer.

So for the GetOrderStats query, we navigate our model and utilize the Col, Prop and Tbl to map the result, and Par to output parameters and apply our filtering. This essentially creates SQL similar to this.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    T1."Name"       AS "Item.CustomerName",
    T1."Email"      AS "Item.CustomerEmail",
    (
        SELECT  COUNT(*)
        FROM    "Order" T0
        WHERE   T0."CustomerId" = T1."Id"
    )               AS "Item.NumberOfOrders"
FROM
    "Customer" T1
WHERE
    T1."Created" >= @P0

This SQL is then read by our accessor, executed through ADO.NET and the results can be mapped to instances of the CustomerStatsView type. Now let's dive into a few more query examples and after that get into how to compose commands.

Queries

The first query we designed was rather simple in its definition, mapping just a few columns to a single data set.

The power of the Razor engine is however that you are able to map any query in a single SQL payload, even if this contains multiple data sets that needs a form of relationship mapping to build its result. To show this, we'll write SQL for our OrderReceiptView which contains one-to-one and one-to-many relationships.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@result OrderReceiptView
@project Customer c
@project Order o
@project CustomerAddress ca
@project CustomerAddress oa
@project OrderLine ol
@project Product p

@{
    var oi = this.R.Open(m => m.Items);

}

-- order/customer/address data set
SELECT
    @o.Col(m => m.Id)          AS @R.Prop(m => m.OrderId),
    @c.Col(m => m.Name)        AS @R.Prop(m => m.CustomerName),
    @c.Col(m => m.Email)       AS @R.Prop(m => m.CustomerEmail),
    @oa.Col(m => m.Id)         AS @R.Prop(m => m.BillingAddress.Id),
    @oa.Col(m => m.Street)     AS @R.Prop(m => m.BillingAddress.Street),
    @oa.Col(m => m.Street)     AS @R.Prop(m => m.BillingAddress.Country),
    @ca.Col(m => m.Id)         AS @R.Prop(m => m.CustomerAddress.Id),
    @ca.Col(m => m.Street)     AS @R.Prop(m => m.CustomerAddress.Street),
    @ca.Col(m => m.Street)     AS @R.Prop(m => m.CustomerAddress.Country)
FROM
    @o.Tbl()
INNER JOIN
    @oa.Tbl() ON @oa.Col(m => m.Id) = @o.Col(m => m.AddressId)
INNER JOIN
    @c.Tbl() ON @c.Col(m => m.Id) = @o.Col(m => m.CustomerId)
LEFT JOIN
    @ca.Tbl() ON @ca.Col(m => m.Id) = @c.Col(m => m.AddressId)

-- orderline data set
SELECT
    @ol.Col(m => m.OrderId)                            AS @oi.Prop(m => m.OrderId),
    @p.Col(m => m.Name)                                AS @oi.Prop(m => m.Product),
    @p.Col(m => m.Price) * @ol.Col(m => m.Units)       AS @oi.Prop(m => m.Total)                           
FROM
    @ol.Tbl()
INNER JOIN
    @p.Tbl() ON @p.Col(m => m.Id) = @ol.Col(m => m.ProductId)

The first thing to take a way from this query are the multiple references to the CustomerAddress table via the @oa and @ca projections. This ensures that there are no mixups in the SQL, as each projection (and table) will have a unique correlated name -- such as "CustomerAddress" T0 and "CustomerAddress" T1.

The next thing is to look at the ways in which we are joining these addresses. An address is required for every Order, so we use the INNER JOIN syntax and optional for a Customer profile requiring a LEFT JOIN.

When mapping these set joins to AddressView instances, the mapper automatically uses the non-null behavior from its [Key] annotation to decide whether or not to create its result.

This means that the CustomerAddress property is only mapped in the case that its Id column contains a non-null value, otherwise the entire property will be null.

Commands

Commands use the same syntax as queries, but without the @result directive. First, let's look at the command for our simple ProductChangePriceModel model from earlier.

1
2
3
4
5
6
7
8
9
@model ProductChangePriceModel
@project Product p

UPDATE
    @p.TblName()
SET
    @p.ColName(m => m.Price) = @M.Par(m => m.NewPrice)
WHERE
    @p.ColName(m => m.Id) = @M.Par(m => m.ProductId)

What you should notice here is the difference between the Col and Tbl extensions that we use for our queries, and the ColName and TblName extensions we use for commands. This is simply to output non-correlated table names, as most database systems do not support the Table T0 syntax for INSERT, UPDATE etc.

1
2
3
4
5
6
UPDATE
    "Product"
SET
    "Price" = @P0
WHERE
    "Id" = @P1

There are two features that are central to writing commands with Razor, the first of which is the ability to batch multiple commands into one. To show an example of this, let's look at the accessor definition for the ChangePrice method.

1
2
3
4
5
6
7
8
9
10
public void ChangePrice(int productId, decimal newPrice)
{
    var model = new ProductChangePriceModel()
    {
        ProductId = productId,
        NewPrice = newPrice,
    };
            
    this.Execute(model);
}

The relationship with ChangePrice.cssql is here very clear, as the method simply passes a single instance of ProductChangePriceModel that is used to create a single UPDATE statement. But say we have a scenario where we want to update multiple products in a single SQL payload. First we'll rewrite the accessor method so it works with lists of product changes instead.

1
2
3
4
5
6
7
8
9
10
public void ChangePrice(int[] productIds, decimal[] newPrices)
{
    var model = productIds.Zip(newPrices, (id, np) => new ProductChangePriceModel()
    {
        ProductId = id,
        NewPrice = np,
    });
            
    this.Execute(model);
}

After this change we are now passing a IEnumerable<ProductChangePriceModel> instance to our Razor command, and it will still work with our existing SQL definition, although only updating the first product in the list.

The reason this still works is that when models are passed between the accessor and Razor, a set of variance rules are used to determine how the accessor's TModel is compatible with the @model type.

Knowing this, let's change the ChangePrice.cssql code to create our batch update. This is done by generating multiple UPDATE statements through a simple foreach and the Vals extension.

1
2
3
4
5
6
7
8
9
10
11
12
@model ProductChangePriceModel
@project Product p

@foreach (var v in this.M.Vals())

{
    UPDATE
        @p.TblName()
    SET
        @p.ColName(m => m.Price) = @v.Par(m => m.NewPrice)
    WHERE
        @p.ColName(m => m.Id) = @v.Par(m => m.ProductId)
}

This feature a powerful tool and really means that whenever you want to modify your database, you can construct object representing not one, but the entire set of changes you want to perform — preferably in a transaction.