Living in the Tech Avalanche Generation

In the first part of this series I looked at how you might go about building an (incredibly tiny) domain specific language for analysing data. The context I gave was a scenario where project managers were required to work with a continuous stream of data in the form of a known schema. This ‘known’ schema is most commonly used in moving and transforming data between various systems in a domain where the central or end target is a Document Management System. The ‘known’ schema is an agreed format that all systems in this particular industry use to extract and subsequently load. It is common to see the project managers struggling with tools like access to compose queries to analyse the data before or after these ETL processes and hence proposition of a DSL.

In the first part I wasn’t particularly happy with the underlying behaviour of the language statements; we were producing a cascading set of queries, rather than composing a query structure. This time around we look to achieve this compositional affect with a fluent interface that issues a beginning statement and subsequently composes a query until a finalizing command is submitted to indicate that execution should take place against the database. If we use Specifications to compose the predicates in querying the database we can achieve the composition through chaining extension methods and thus build up an execution context from the syntax in our language.

Consider this fluent interface:

public void prints_order_lines_grouped_by_customer_and_orders()
{
    NorthwindLangQuery
    .BeginQuery()
    .Customers()
    .With_An_Id_Equal_To(“ALFKI”)
    .With_Orders_Shipped_From(“Germany”)
    .That()
    .Are()
    .Older_In_Years_By(2)
    .EndCustomerQuery()
    .Print_Customer_Orders_To_Console_Window();
}

Instead of cascading sub queries that work on an initial database query as with the code shown in part 1, we use the specification pattern to build up predicates that we submit with a single SQL (LINQ To Entities) query to the database. If we pull apart the NorthwindLang query above and follow it method by method here is what it looks like:

public static NorthwindLang BeginQuery()
{
    _customerSpec =
        new Specification<Customer>(c => c == c);

    _orderSpec =
        new Specification<Order>(o => o == o);

    _orderLineSpec =
        new Specification<OrderLine>(ol => ol == ol);
    return new NorthwindLang();
}

The BeginQuery() method simply primes each of our specifications which progressively are built upon in the following chained method calls.

No change required in the method body below as we already know we are dealing with a customer.

public static Specification<Customer> Customers(this NorthwindLang query)
{
    return _customerSpec;
}

Next the specification has a filtering expression to the customer ID appended using the logical AND operator.

public static Specification<Customer> With_An_Id_Equal_To
            (this Specification<Customer> customers, string id)
{
    var cust_id_spec =
        new Specification<Customer>(c => c.CustomerID == id);
    _customerSpec &= cust_id_spec;
    return _customerSpec;
}

The customer orders are also required in the query so we are going to need to setup the Orders specification with it’s required predicate.

public static Specification<Order> With_Orders_Shipped_From
            (this Specification<Customer> customers, string country)
{
    Specification<Order> order_shipcountry_spec =
        new Specification<Order>(o => o.ShipCountry == country);
    _orderSpec &= order_shipcountry_spec;
    return _orderSpec;
}

The next two parts of the query are benign and have no effect on the composition of the query and only play a part in providing a more natural syntax composition for the language.

public static Specification<Order> That
            (this Specification<Order> orders)
{
    return _orderSpec;
}

public static Specification<Order> Are
    (this Specification<Order> orders)
{
    return _orderSpec;
}

Next we add a further Orders specification limiting the result set to orders more than twelve years old.

public static Specification<Order> Older_In_Years_By
            (this Specification<Order> orders, int age)
{
    var date = DateTime.Now.AddYears(-age);
    Specification<Order> order_older_spec =
        new Specification<Order>(o => o.OrderDate < date);
    return _orderSpec &= order_older_spec;
}

Finally we demarcate the end of the query by calling one of the finalization methods that can only be followed by subsequent printing methods.

public static IEnumerable<LangDomain.Customer>
    EndCustomerQuery(this Specification<Order> orders)
{
    return DomainQuery<LangDomain.Customer>
        .CreateQuery(_customerSpec, _orderSpec);
}

This is still all proof of concept stuff and I plan to follow up with putting a harness together with the Fireball code editor API. Here is a further list of some of the other language statements that the fluent interface is capable of producing at the time of this writing.

public void prints_order_lines_grouped_by_customer_and_orders()
{
    NorthwindLangQuery
        .BeginQuery()
        .Customers()
        .With_An_Id_Equal_To(“ALFKI”)
        .With_Orders_Shipped_From(“Germany”)
        .That()
        .Are()
        .Older_In_Years_By(2)
        .EndCustomerQuery()
        .Print_Customer_Orders_To_Console_Window();
}

public void selects_customers_by_id()
{
    var cust = NorthwindLangQuery
        .BeginQuery()
        .Customers()
        .With_An_Id_Equal_To(“ALFKI”)
        .EndCustomerQuery()
        .As_Unique_Where_Customers_Ids_Are_The_Same();

    foreach (var c in cust)
    {
        Console.WriteLine(c.CompanyName);
    }
}

public void selects_orders_specified_by_age_of_orders_with_
    specified_freight_costs()
{
    var query = NorthwindLangQuery
        .BeginQuery()
        .Orders()
        .Older_In_Years_By(12)
        .With()
        .Freight_Costs_More_Than(360)
        .EndOrderQuery();

    foreach (var o in query)
    {
        foreach (var ol in o.Order_Details)
        {
            Console.WriteLine(o.OrderID +
                ” : “ + o.Freight +
                ” : “ + ol.ProductID);
        }
    }
}

public void selects_customers_by_id_for_advanced_users()
{
    var cust = NorthwindLangQuery
        .BeginQuery()
        .Customers()
        .EndCustomerQuery()
        .As_Unique_Where((cLeft, cRight) =>
            cLeft.CustomerID == cRight.CustomerID);

    foreach (var c in cust)
    {
        Console.WriteLine(c.CompanyName);
    }
}

You can download the entire source code from the repository here.

I have been giving some thought lately  to how we might go about writing a small internal DSL for reporting on and or editing data in database. Immediately I thought about how LINQ (a DSL in its own right) might play it’s part in this and since we are talking Data, then the Entity Framework came immediately into consideration. Why should we have a DSL for accessing Data? It’s a commonly held opinion that SQL is a DSL for that very purpose, albeit a very general purpose DSL and LINQ is certainly a DSL.

Let’s consider the workers of the Northwind Trading company as an example of some possible beneficiaries to such a Data Centric DSL modelled around a specific business. Of course workers at Northwind have MS Access and SQL Server GUI tools (often called Graphical DSL’s), to help design their queries.

A snippet

So what would our Northwind language look like? Given that we set up from the start to consider an internal DSL, I have chosen to use C# to implement this “little language” (very little) and we will use a fluent interface to accommodate our design. A query might be expressed like this:

public void Do()
{
    var custs = NorthwindLang
        .Customers
        .WithOrdersShippedFrom(“Germany”)
        .That()
        .Are()
        .OlderInYearsBy(5);
}

The Language

public static class NorthwindLang
{
    public static Customer[] Customers;
    private static NorthwindEntities ctx = null;

    public static Customer[] WithOrdersShippedFrom(this Customer[] customers,
        string country)
    {
        ctx = new NorthwindEntities();
        var customers_and_orders =
           (ObjectQuery<Customer>)
           from c in ctx.Customers.Include(“Orders”)
           where c.Orders.Any(o => o.ShipCountry == country)
           select c;

        Console.WriteLine(customers_and_orders.ToTraceString());

        customers = customers_and_orders.ToArray();

        customers.ToList()
            .ForEach(c => c.Orders.ToList()
                .ForEach(o => {
                    if (o.ShipCountry != country)
                    {
                        ctx.Detach(o);
                    }}));

        return customers;
    }

    public static Customer[] That(this Customer[] customers)
    {
        return customers;
    }

    public static Customer[] Are(this Customer[] customers)
    {
        return customers;
    }

    public static Customer[] OlderInYearsBy(this Customer[] customers, int years)
    {
        var years_ago = DateTime.Now.AddYears(-years);

        var newOrds = new EntityCollection<Order>();

        var coolFunc =
            new Func<IEnumerable<Order>,
                EntityCollection<Order>>(o =>
            {
                newOrds = new EntityCollection<Order>();
                o.ToList().
                    ForEach(ord => { ctx.Detach(ord);
                        newOrds.Add(ord); });
                return newOrds;
            });

        var filtered_customers =
            from c in customers
                 let neworders =
                      (from o in c.Orders
                      where (o.ShippedDate.HasValue) &&
                            (o.ShippedDate.Value < years_ago)
                      select o)
                 select new Customer()
                 {
                     CustomerID = c.CustomerID,
                     CompanyName = c.CompanyName,
                     Orders = coolFunc(neworders)
                 };

        customers =
            filtered_customers
            .Distinct()
            .ToArray();

        return customers;
    }
}

Problematic

Something worth noting about the OlderInYearsBy() method is the projection taking place with the Orders property assignment for each new Customer entity projected in the queries select clause. You can see that we are “projecting” the orders that match the criteria into the customers Orders collection, however as they have already been fetched from the Database and are attached to an ObjectContext, we need to detach them so they can be referenced by a new Customer Entity that is being returned in the array of Customers produced by the function. Also, you may have observed that we are working with extensions to Customer arrays and not EntityCollection<T> where T is of type Customer, further to that, the code presented is filtering data selected from method calls higher up the call chain of the fluent interface and their is nothing stopping a user from composing a query that does not work with the assumptions present in the fluent interfaces design, and consequently the behaviour may not be as expected. Another issue with this approach is grammar and creating sentences that make little sense and don’t adhere to the DSL’s intended use or rules.

public void Do3()
{
    var custs = NorthwindLang
        .That()
        .OlderInYearsBy(15)
        .WithOrdersShippedFrom(“Italy”)
        .Are()
        .That()
        .OlderInYearsBy(15);
}

Where to from here?

I want to follow up with a post on how to achieve similar outcomes using a functional approach and building compound queries using lambda expression that have been joined together with the logical & and | operators. A perfect candidate for this requirement is (dare I say it) the specification pattern. What I hope to achieve with this more functional approach is to be able to compose sentences of intent that build our query so that it can be submitted as one single SQL query. The issue of grammar still remains a problem and I have considered previously that a graphical designer that sits in front of a fluent interface might offer a solution and offer some options with respect to constraining the use of the fluent interface. Consider a UI design surface built with the VSX DSL tools that sits on top of our NorthwindLang fluent interface. Anyway we shall see where this journey takes us!

One final thing

I don’t recommend using an ORM for reporting purposes. The scenario under which this idea was proposed (rather envisioned) was one where the database in question is a constantly renewed small set of data that is exported from an enterprise Document Management System and it’s purpose is to provide an offline view of partitioned data relevant to a consultants work for a given client. This precludes the requirements from having to deal with issues such as concurrency, given that each instance of this database is usually single user in the most literal sense.

I am thinking. Stop that you say? Seriously, I have been wondering about combining a fluent interface with a some VS.Net VSX visual DSL tooling. I am currently working a small internal domain specific language and it occurred to me that rather than leaving the only method of access to the DSL as raw code, that perhaps by building out some visual entities around the entities expressed through the DSL, I might just be able to build a platform and not just the DSL on it’s own. So if we have code like:

3.Month.Old.Documents(“Contract”)

created by a designer like experience along of the lines of this:

Just having preliminary thoughts about this at the moment and I can already see that the vocabulary of DSL runs the risk of being influenced by the UI design in way that obscures and corrupts its meaning. For example should it read:

//makes sense
3.Day.Old.etc
//seems awkward and clunky
3.Day.InFuture

A quick revision to the Fluent Interfaces and Designer and we might get this:

Ok so we can start to think about all sorts of things when it comes to the readability of the language underneath the designer but my next question is what about constraints? Should we have any? Does it feel right to leave the user the ability to define a design that would produce this:

3.Years.Now.etc.etc

But I don’t want to start building constraints around what would amount to business logic concerns now, or do I? A lot more thought is required on this whole topic I think.