Living in the Tech Avalanche Generation

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.