Efcore: 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?

[smitpatel]

Aggregates are considered ResultOperators in ReLinq and for supporting a single aggregate function means, adding corresponding CustomResultOperator. Registering it in query parser and then translating it in VisitResultOperator method. It is neither easy nor scalable for multiple operators.

It is true that adding function for Stdev in EF has value but due to lack of good underlying support, I re-purposed this issue to add back-end support first. Once that is done, I will open another issue to track specific work for Stdev. (or it could be added as proof of concept that pipeline works). My main motivation for re-using same issue was, avoid having multiple tracking issues which talks about adding more functions which would have been essentially blocked on this one.

[pmiddleton]

@smitpatel - I was thinking that it might be possible to add a single ResultOperator that can deal with all custom aggregates. That way we could deal with custom clr aggregate functions in Sql Server also.

If that isn't going to work then just supporting the big hitters should still be possible.

@roji - does postgres support STDEVP, VAR, VARP? Those are the ones from Sql Server which are still missing.

[roji]

Makes sense - it does seem more important to add infrastructure for provider-defined (and even user-defined?) aggregate functions than to add ad-hoc support for STDDEV. At the same time if it's really trivial to add stuff like STDDEV alongside sum and average, why not...

Here are the PostgreSQL docs for aggregate statistical functions (note that the page lists all aggregate functions, not just statistical, so scroll around). STDEVP is supported (it's called stddev_pop) as well as variance and var_pop, and there are some other interesting ones in there as well.

[SteffenMangold]

Can someone tell me what the process is after "up-for-grabs"? Only to know about what time horizon we are talking. :)

[divega]

@SteffenMangold This is the backlog. It means it is definitively not going into 2.1. It may go into the next release if someone (e.g. @pmiddleton has done a lot of great contributions around function support in EF Core) picks it up. Otherwise we may choose to do it anyway in the next release, but given other important features in the backlog or already assigned to the 2.2 milestone, I think the chances are small.

In the meanwhile my recommendation would be to write the SQL in FromSql(). With the improvements included in 2.1, you should be able to declare a query type in your model that contains the result of the aggregate and that you can use for context.Query().FromSql("...").

[pmiddleton]

I will be able to look at this for the 2.2 milestone.

[roji]

@pmiddleton just to be sure, this would include provider-facing infrastructure (similar to today's existing method and member translators), and not just user-facing DbFunction support, right?

[pmiddleton]

@roji

It's all up in the air right now, as I have not started, so we can shoot for whatever we want.

Are you thinking of an interface for providers to preregister known aggregates so users don't have to. Similar to how base functions are added in Relational via IMethodProvider?

[roji]

That's right. For stuff like STDDEV and others, which exist on some providers but not on others, we shouldn't require to use DbFunction but rather have the provider translate something like EF.Functions.Stddev(), if at all possible... Ideally something like the existing SqlServerCompositeMethodCallTranslator.

[SteffenMangold]

Did somebody already picked that task?

[pmiddleton]

The query pipeline is being rewritten in 3.0 so this is currently blocked by that work.

[SteffenMangold]

Is 3.0 the next major release or is there some other 2.x coming before? Just want to estimate if 1 or 5 years. ^^

[ajcvickers]

@SteffenMangold 3.0 is the next major release, that is after 2.2.

[smitpatel]

See #20052

[roji]

Just some extra details on aggregate functions...

Some aggregate functions accept some additional non-set arguments. For example, PG string_agg concatenates a set of strings, and also accepts a delimiter (SELECT string_agg(name, ',') FROM users). Pretty trivial, just mentioning it.

More interesting: there are some cases of functions accepting multiple sets; a good example is jsonb_object_agg, which reduces two arbitrary sets of values into a single json document:

postgres=# select jsonb_object_agg('key_' || x::text, x) from
generate_series(1,4) as x;
                 jsonb_object_agg
--------------------------------------------------
 {"key_1": 1, "key_2": 2, "key_3": 3, "key_4": 4}
(1 row)