Efcore: Add LINQ methods for INTERSECT ALL and EXCEPT ALL

[roji]

We support the four standard set operations (UNION, UNION ALL, INTERSECT, EXCEPT), but PostgreSQL also supports INTERSECT ALL and EXCEPT ALL (which don't return distinct elements, much like UNION ALL). Our current design uses an enum to represent the operation type, so it's not easily extensible by providers. Options include:

1. Having an Other enum value which allows relational to understand it's a set operation, but also allows providers to specify additional information on its SelectExpression that would allow the correct operation to be rendered to SQL.
2. Have a SetOperationExpression deriving from SelectExpression, allowing providers to derive from that. However, I'm not clear on the extension/inheritance architecture with regards to providers (who should also be able to extend SelectExpression in order to override logic there?).

Actual need for INTERSECT ALL and EXCEPT ALL isn't likely to be high, so we can also punt this (although it's a pretty fundamental design discussion).

Unrelated note: MySQL only supports UNION and UNION ALL, no INTERSECT or EXCEPT.

[divega]

FWIW, UNION ALL, UNION DISTINCT, EXCEPT ALL, EXCEPT DISTINCT, INTERSECT ALL, and INTERSECT DISTINCT are all ANSI SQL standard operators. The DISTINCT keyword is optional.

[roji]

Great! In that case maybe we can simply include them in our enum and be done with this?

The only issue with EXCEPT ALL and INTERSECT ALL is that there's no LINQ operator for them, so we'd have to add something on EF.Functions or similar?

[mburbea]

Sql server does not support these operators, but a translation for intersect all/except all could be done as so::

select a,b,c from table1 t1
intersect all
select d,e,f from table2 t2

Is equivalent to::

select a,b,c from table1 t1
where exists (
       select a,b,c
       intersect
       select d,e,f from table2 t2
)

[divega]

@roji I think it would be okay to come up new IQueryable@mburbea suggests. But it all seems low priority for now.

[smitpatel]

Infrastructure changes made to design now supports following.

• Union
• Except
• Intersect
  & all 3 of them with/without Distinct so all 6.

Pending work here is

• Add public API
• Add provider specific translation when set operation is not available on server