Object-Oriented SQL Representation

Question

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();

I'd expect 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.

Further parameters:

• 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.

Answer 1

I can recommend SqlKata for this purpose. It's a .NET library that helps in building SQL queries for different DB engines and optionally to execute them, by using a similar syntax to what you posted.

About your requirements:

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)

It lets you call methods any way you want, building the same SQL query. It supports all those options, but check out their documentation for further details

without caring about adding whitespace and brackets in the right places

Since the SQL query is built by the library, you don't have to worry about that. You do need to take care when you insert arbitrary SQL fragments, however. About brackets, it puts the correct delimiters according to the selected target DB engine.

picking table aliases

Here is where the library falls short. The table aliases are choosen by the user and are not auto generated. You need to define and respect them though the query.

Must be usable in C# on the .NET Framework, i.e. a .NET library

It's a .NET library and can therefore be used in any .NET language.

Should output SQL compatible with MS SQL Server and Oracle (possibly by specifying the target dialect as an option)

It supports them both, as well as SQLite, MySql, PostgreSql and Firebird.

License must allow usage in commercial applications

It uses the MIT License, which allows commercial usage.