SQL Server: Translate Math.Max/Min in non-aggregate context

[yesmey]

Today Math.Max / Math.Min is not being translated for SQL Server - only for sqlite as introduced in #10533

Transact-SQL does not have an MIN or MAX function, so there's no 1-to-1 mapping, but it is however possible to translate it into a CASE WHEN statement.
For example

Math.Max(Column1, Column2)

is equivalent to

CASE
    WHEN Column1 >= Column2 THEN Column1
    ELSE Column2
END

---

As a suggestion, I made a reference implementation by adding the following translation to SqlServerMathTranslator.cs

For Math.Max it looks like this:

var left = arguments[0];
var right = arguments[1];

var typeMapping = ExpressionExtensions.InferTypeMapping(left, right);
left = _sqlExpressionFactory.ApplyTypeMapping(left, typeMapping);
right = _sqlExpressionFactory.ApplyTypeMapping(right, typeMapping);

return _sqlExpressionFactory.Case(
    new[] { new CaseWhenClause(_sqlExpressionFactory.GreaterThanOrEqual(left, right), left) },
    right);

and Math.Min:

// same type mapping...
return _sqlExpressionFactory.Case(
    new[] { new CaseWhenClause(_sqlExpressionFactory.LessThanOrEqual(left, right), left) },
    right);

Link to my branch of the example

[AraHaan]

I though TSQL did have an Min and Max operation, but are Aggregate functions that can be used in normal queries in a special way.

[yesmey]

Oh yes, I meant that no scalar version of that function exists

[roji]

SQL Server is introducing the GREATEST and LEAST functions, which are standard across relational databases (see announcements). These are already available on Azure SQL, and will apparently be available in the next version of on-premise SQL Server.

So while they aren't generally available (except on Azure), I'm not sure it makes sense to introduce the CASE/WHEN translation proposed above (we'll probably want to add GREATEST/LEAST translations when they're available). Note that it should be easy to do the above mapping with user-defined function mapping.

[yesmey]

@roji I hadn't heard about this, that's great! Yes, my proposal more of a hack, it makes sense if you'd want to wait for those to come avaliable instead

[AraHaan]

SQL Server is introducing the GREATEST and LEAST functions, which are standard across relational databases (see announcements). These are already available on Azure SQL, and will apparently be available in the next version of on-premise SQL Server.

So while they aren't generally available (except on Azure), I'm not sure it makes sense to introduce the CASE/WHEN translation proposed above (we'll probably want to add GREATEST/LEAST translations when they're available). Note that it should be easy to do the above mapping with user-defined function mapping.

Wait so SQL Server 2022 is a thing now?

[roji]

It will certainly be a thing at some point...

[MKatGH]

Am I missing some deeper logic? The code below would do just that 'out of the box' in ef.core.

public class Demo
{
    public int Value1 { get; set; }
    public int Value2 { get; set; }
}

var r = this.Context.DemoRecords
    .Select(e => new
    {
        MinValue = e.Value1 > e.Value2 ? e.Value2 : e.Value1,    // Like Math.Min(e.Value1, e.Value2)
        MaxValue = e.Value1 > e.Value2 ? e.Value1 : e.Value2    // Like Math.Max(e.Value1, e.Value2)
    })
    .ToList();

Ok, I'll give you that 'Math.Max(Column1, Column2)' is a little bit shorter, but not by that much.
Support for GREATEST and LEAST would be better, since that will support multiple arguments. Math.Max is limited to two.

[yesmey]

@MKatGH you're correct, it would just be for convenience purposes. string.IsNullOrEmpty for example is also trivial to express out of the box, but there is a translator for that. I don't really have a strong opinion for or against it, it's just a mere suggestion