Allow DbFunction to return scalar value from aggregates

[SteffenMangold]

There is no solution for calling STDEV aggregation function from Entity Core.

[smitpatel]

There is no LINQ operator for that. But it would be easy to write a user defined DbFunction to call & translate to STDEV on server.

[bricelam]

Should we provide one?

Dim stdev = Aggregate b In db.Benchmarks Into EF.Functions.Stdev(b.Result)

[SteffenMangold]

That would be great 🤗

[bricelam]

Notes for triage: PostgreSQL has stddev; SQLite has none.

[smitpatel]

Re-purposing the issue. Currently there is no support to write a DbFunction which translates to aggregate function over server. Same restriction goes for our method call translators. We need to lift this restriction before we can translate aggregate functions on server.

[pmiddleton]

The heart of the issue is that ReLinq won't recognize the function as a ResultOperator by default. We will have to integrate to get the methods translated correctly, and then deal with the functions during query creation.

[SteffenMangold]

Is it some kind of a big deal? I try to investigate a realistic timeframe for this. Just to know how to go on with our project. Thanks for your interest so long! ,😊

[bricelam]

It's not too bad to work around using query types and FromSql:

class ScalarResult<T>
{
    public T Value { get; set; }
}

class MyContext : DbContext
{
    public DbSet<Benchmark> Benchmarks { get; set; }
    public DbQuery<ScalarResult<double>> Doubles { get; set; }
}

var db = new MyContext();
var stdev = Enumerable.Single(
    from r in db.Doubles.FromSql("SELECT STDEV(Result) AS Value FROM Benchmarks")
    select r.Value);

[roji]

I know this issue has been repurposed, but there seems to definitely be value in having a EF.Functions.Stdev() which would be translated to SQL by providers which support it (we have at least SQL Server and PostgreSQL). We can open a different issue to track this.

On a related note, how easy is it for providers to add translation for additional aggregate functions? Regular functions are quite simple (via IMethodCallTranslator) but methods such as Sum and Average seem to receive some pretty special handling in the core. Is it possible (or is it somehow planned) to add support for translating aggregate functions with the same ease as regular ones?

[pmiddleton]

@roji

Aggregates are treated special by both ReLinq and Core so supporting them will require some extra changes in the backend.

I will take a look at this after we finish with merging in TVFs.

@divega - Should we use this item to track or open another?