SQL Server: support bitwise NOT on boolean values

[ranma42]

For boolean expressions, bitwise ~ and logical NOT are generally the same, but in SqlServer the first can be applied on BITs, while the second one is for boolean predicates.

Since #34080 the SqlServer provider avoids converting to a predicate when it just needs to negate a BIT, but it does so using ^ 1.
A better translation could be emitted by translating to a ~.

[roji]

Just noting that bitwise NOT is useful also for non-boolean types, so this would be useful beyond the specific SQL Server scenario we've been focusing on (and beyond SQL Server in general).

[ranma42]

Non-boolean types can already use the bitwise not:

efcore/src/EFCore.Relational/Query/QuerySqlGenerator.cs

Lines 883 to 885 in 73db4f0

	case ExpressionType.Not:
	{
	_relationalCommandBuilder.Append("~");

The boolean case is explicitly treated as special:

efcore/src/EFCore.Relational/Query/QuerySqlGenerator.cs

Lines 856 to 874 in 73db4f0

	case ExpressionType.Not
	when sqlUnaryExpression.Type == typeof(bool):
	{
	switch (sqlUnaryExpression.Operand)
	{
	case InExpression inExpression:
	GenerateIn(inExpression, negated: true);
	break;
	case ExistsExpression existsExpression:
	GenerateExists(existsExpression, negated: true);
	break;
	case LikeExpression likeExpression:
	GenerateLike(likeExpression, negated: true);
	break;
	default:
	_relationalCommandBuilder.Append("NOT (");

The current encoding does not differentiate between bitwise and logical not (well, on boolean expressions it is always the logical not, on integer expressions it is always the bitwise not).

[ranma42]

I am thinking about working on this. My approach would be to extend SqlUnaryExpression to also allow OnesComplement.
In the first iteration, I would probably only use it in SqlServer to encode this specific not (changing other parts might needlessly affect other providers).