I am looking for a library that offers an object-oriented representation of raw SQL queries. With the example of
SELECT statements, I imagine something like having an object that represents the statement, then being able to add tables to join in via a method, adding columns to output based on the aforementioned tables, etc.
Something like in this pseudo-code:
var query = new SelectQuery(); var t1 = query.AddTable("TABLE1"); var t2 = query.AddTable("TABLE2"); query.AddEqualityRestriction(new ColumnReference(t1, "ID"), new ColumnReference(t2, "TABLE1ID")); query.AddOutputColumn(new ColumnReference(t1, "NAME")); var sql = query.ToString();
sql to contain something like
SELECT a.NAME FROM TABLE1 a JOIN TABLE2 b ON a.ID = b.TABLE1ID
in the end.
Obviously, neither the concrete structure of calls nor the identifiers have to look exactly like this. Anything that lets me add tables, restrictions, projected columns, subqueries, etc., in an arbitrary order (i.e. not necessarily sequentially, as they appear in the resulting SQL), and without caring about adding whitespace and brackets in the right places or picking table aliases is fine.
Note that I am not looking for a full-fledged object-relational mapper. The target application I need this for already uses Entity Framework, but with many disjoint schemas on a per-module basis. I am looking to build queries (for which I already know the concrete table and column names) that can reach globally across our entire DB schema.
- Must be usable in C# on the .NET Framework, i.e. a .NET library.
- Should output SQL compatible with MS SQL Server and Oracle (possibly by specifying the target dialect as an option).
- License must allow usage in commercial applications.