The new SQL backend should both be a good example of how to approach SQL generation using the new re-linq front-end (QueryModel, usw.) and provide a sound, extensible architecture backing re-store's LINQ provider.
It will sport SQL generation in three phases:
LINQ providers will be able to hook into each of the three steps for customization, optimization, and advanced query transformations.
Support queries of the forms: select c.IsStarredCook, select c.FirstName == "John", where c.IsStarredCook, where c.IsStarredCook == (c.FirstName == "John"), select true, where false || (c.FirstName == "John"), and similar
The SQL generated for such queries depends on the context of the boolean expression. "True" and "False" constants are represented as 1 and 0 values; when a boolean expression should be used as a value, a CASE WHEN expression is emitted; when a boolean column should be used as a predicate, it is compared to 1.
Support for queries of the form: from s in Students from s2 in Students select ...
This query results in a SQL query with multiple from table sources separated by CROSS JOIN.
Support for queries of the form: from s in Students from s2 in s.Friends select ...
This query results in a SQL query with multiple from table sources separated by INNER JOIN.
Support for queries of the form: from s in ... where s.ID == 0 where s.FirstName == "hugo" select s
This generates a single SQL WHERE clause; the conditions are combined via AND.
Support for expressions of the form: (from s in ... select s).Count(), etc.
Such result operators insert COUNT, DISTINCT, and TOP clauses in the generated SQL statement.
Support for expressions of the form: select !(s.FirstName == "Hugo"), -s.Age, +s.Age.
The SQL generated for these expressions contains a SQL NOT expression, -, and +.
Support for expressions of the following form: select s.FirstName + " " + s.LastName.
The SQL generated for this expression is a SQL concatenation. Only binary operators also provided by SQL are supported, other operators yield an exception.
If the select expression of a query yields a boolean value, a CASE SQL expression is generated, returning 1 for true expressions and 0 for untrue expressions.
If the expression compares a column or value with the constant value NULL, an IS NULL or IS NOT NULL SQL expression is generated.