LINQ-like or SQL-like DSL for end-users to run queries to select (not modify) data?

Question

For a utility I'm working on, the client would like to be able to generate graphic reports on the data that has been collected. I can already generate a couple canned graphs (using ZedGraph, which is a very nice library); however, the utility would be much more flexible if the graphs were more programmable or configurable by the end-user.

TLDR version

I want users to be able to use something like SQL to safely extract and select data from a List of objects that I provide and can describe. What free tools or libraries will help me accomplish this?

Full version

I've given thought to using IronPython, IronRuby, and LuaInterface, but frankly they're all a bit overpowered for what I want to do. My classes are fairly simple, along the lines of:

class Person:
    string Name;
    int HeightInCm;
    DateTime BirthDate;
    Weight[] WeighIns;

class Weight:
    int WeightInKg;
    DateTime Date;
    Person Owner;

(exact classes have been changed to protect the innocent).

To come up with the data for the graph, the user will choose whether it's a bar graph, scatter plot, etc., and then to actually obtain the data, I would like to obtain some kind of List from the user simply entering something SQL-ish along the lines of

SELECT Name, AVG(WeighIns) FROM People
SELECT WeightInKg, Owner.HeightInCm FROM Weights

And as a bonus, it would be nice if you could actually do operations as well:

SELECT WeightInKg, (Date - Owner.BirthDate) AS Age FROM Weights

The DSL doesn't have to be compliant SQL in any way; it doesn't even have to resemble SQL, but I can't think of a more efficient descriptive language for the task.

I'm fine filling in blanks; I don't expect a library to do everything for me. What I would expect to exist (but haven't been able to find in any way, shape, or form) is something like Fluent NHibernate (which I am already using in the project) where I can declare a mapping, something like

var personRequest = Request<Person>();
personRequest.Item("Name", (p => p.Name));
personRequest.Item("HeightInCm", (p => p.HeightInCm));
personRequest.Item("HeightInInches", (p => p.HeightInCm * CM_TO_INCHES));
// ...
var weightRequest = Request<Weight>();
weightRequest.Item("Owner", (w => w.Owner), personRequest); // Indicate a chain to personRequest
// ...
var people = Table<Person>("People", GetPeopleFromDatabase());
var weights = Table<Weight>("Weights", GetWeightsFromDatabase());
// ...
TryRunQuery(userInputQuery);

LINQ is so close to what I want to do, but AFAIK there's no way to sandbox it. I don't want to expose any unnecessary functionality to the end user; meaning I don't want the user to be able to send in and process:

from p in people select (p => { System.IO.File.Delete("C:\\something\\important"); return p.Name })

So does anyone know of any free .NET libraries that allow something like what I've described above? Or is there some way to sandbox LINQ? cs-script is close too, but it doesn't seem to offer sandboxing yet either. I'd be hesitant to expose the NHibernate interface either, as the user should have a read-only view of the data at this point in the usage.

I'm using C# 3.5, and pure .NET solutions would be preferred.

The bottom line is that I'm really trying to avoid writing my own parser for a subset of SQL that would only apply to this single project.

Answer 1

There is a way to sandbox LINQ or even C#: A sandboxed appdomain. I would recommend you look into accepting and compiling LINQ in a locked-down domain.

Regarding NHibernate, perhaps you can pass the objects into the domain without exposing NHibernate at all (I don't know how NHibernate works). If this is not possible, perhaps the connection to the database used within the sandbox can be logged in as a user who is granted only SELECT permissions.

Answer 2

Hey Mark, Maybe the expressions will come handy for You. You could provide simple entry places for: a) what to select - user is expected to enter an expression only _ probably member and arithmetic expressions - those are subclasses of the expression class b) how to filter the things = again only expressions are expected c) ordering d) joining?

Expressions don't let You do File.Delete because You operate only on precise domain objects (which probably don't have this functionality). The only thing You have to check is whether the parameters of the said expressions are of Your domain types. and Return types of said expressions are of domain types (or generic types in case of IEnumerable<> or IQuerable<>

this might prove helpful
I.E. expressions don't let You write multi-line statements.

Then You build your method chain in code and voila. There comes the data

Answer 3

I ended up using a little bit of a different approach. Instead of letting users pick arbitrary fields and make arbitrary graphs, I'm still presenting canned graphs, but I'm using Flee to let the user filter out exactly what data is used in the source of the graph. This works out nicely, because I ended up making a set of mappings from variable names to "accessors", and then using those mappings to inject variables into the user-entered filters. It ended up something like:

List<Mapping<Person>> mappings;
// ...
mappings.Add(new Mapping("Weight", p => p.Weight, "The person's weight (in pounds)"));
// ...
foreach (var m in mappings)
{
    context.Variables[m.Name] = m.Accessor(p);
}
// ...

And you can even give an expression context an "owner" (think Ruby's instance_eval, where the context is executed with score of the specified object as this); then the user can even enter a filter like Weight > InputNum("The minimum weight to see"), and then they will be prompted thusly when the filter is executed, because I've defined a method InputNum in the owning class.

I feel like it was a good balance between effort involved and end result. I would recommend Flee to anyone who has a need to parse simple statements, especially if you need to extend those statements with your own variables and functions as well.