LINQ to SQL - calling built-in aggregate functions (i.e. STDEV)

Question

Is there any way to have LINQ translate queries directly to functions like SQL's STDEV? For example, the LINQ

from t in table
group t by t.something into g
select new {
    avg = g.Average(o => o.number)
    stdev = g.?????
}

gets turned into a SQL AVG. However, there is no support for standard deviation in LINQ.

One approach was suggested here: Standard Deviation in LINQ

However, this is clunky and it also requires a bit of additional work to handle possible null values, which the sql STDEV function automatically ignores for you. Additionally, it results in less data being sent over the wire and it's faster to compute.

Any way to do this?

score 0 · Accepted Answer · answered Aug 06 '12 at 19:47

No, there's no way to do it directly through LINQ.

You could create a CLR aggregate which really just wraps the STDEV aggregate in SQL Server, but that would be a ton of excessive work (although it is possible).

However, the option of least resistance is going to be for each query that you want the standard deviation on, you would create a stored procedure which performs the operations and then use LINQ-to-SQL to call that.

Or, if you want to use composition in LINQ-to-SQL, you can create a user-defined table function in SQL server and then access that in LINQ-to-SQL and compose your query using that.

I ended up going with a stored procedure. Thanks. – Andrew Mao Aug 14 '12 at 00:41 — Andrew Mao, Aug 14 '12 at 00:41

LINQ to SQL - calling built-in aggregate functions (i.e. STDEV)

1 Answers1