npgsql/Npgsql — pull request #256 by Emill (2014-06-14 01:42:36+00:00)
Title: Entity Framework: Fix relational expressions, column referencing, column name aliasing, implement DbApplyExpression and DbInExpression

When I first started implementing support for DbApplyExpression in #224, I realized that there were too many bugs in the code base to make this easy. So I made some thorough research how CommandTrees can be structured and implemented a new way how these are translated to SQL.

From previously doing everything a bit ad-hoc, my strategy is instead to always return a (sub)query from a command subtree that, if compatible, can be altered so it doesn't need to be wrapped in another subquery when it is used as a child node.

For example, Scan(some_table) just returns

SELECT ? FROM some_table

and a Limit(Scan(some_table), 3) returns

SELECT ? FROM some_table LIMIT 3

but a Filter(Limit(Scan(some_table), 3), y < 4) returns

SELECT ? FROM (SELECT ? FROM some_table LIMIT 3) AS x WHERE x.y < 4

since limit is not compatible with filter (in that direction). Previously, the compatibility checks were missing so these kinds of queries like the one above would often be written incorrectly. Look for example in #246. Note that ? means the columns that will be projected. Only referenced columns will be projected. As soon as a reference is visited, the column is marked for projection through all the subqueries down to the current expression. Previously, only references in the projection part of DbProjectExpressions and join clauses were considered, which turns out to be far from enough.

Now, if name clashes occurs in Joins, i.e. both two sources define columns with the same name that are later used in a projection, this will also be handled by aliasing one of the columns. This was not done before at all.

A DbApplyExpression is generated as a LATERAL JOIN. Outer Apply becomes "LEFT OUTER JOIN LATERAL (...) AS name ON TRUE" and Cross Apply becomes "CROSS JOIN LATERAL (...)". The subquery can reference columns in the left hand side of the join expression.

I also noticed that Union All/Except/Intersect was missing parentheses around the two operands. Then in code like

SELECT ... UNION ALL SELECT ... LIMIT x

the LIMIT clause applies for the unioned result, and not for the right operand. Now it instead correctly generates

(SELECT ...) UNION ALL (SELECT ... LIMIT x)

I also implemented support for DbInExpression. That means queries like

int[] arr = new int[] {1,2,3,4};
context.Table.Where(x => arr.Contains(x.number));

will generate SQL like

SELECT ... FROM Table WHERE Table.number IN (1,2,3,4)

instead of combined OR expressions.

This pull request fixes #224, #243, #246 and probably also #216, #149, #156 and a lot of other bug reports in other channels.

I added some test queries as well to the unit tests. However, simply running them will only check that it didn't throw exceptions or generated bad SQL, not that it actually generated semantically correct SQL according to the input queries. Therefore I added logging so the generated SQL can be studied in "Text Output" tab in NUnit.

Please let me know what you think or if you have queries that don't work with this commit.