0

Suppose that there is a DataTable dt of type double with the columns "x","y","z" in C#.

While values are filled into dt, it certainly occurs that only two dimensions are assigned a being assigned which intentionally leaves the third vacant with DBNull.Value.

When computing the average for a dimension with a filter according to Transact-SQL syntax things work properly:

X = (double)dt.Compute("Avg([x])", "x IS NOT NULL");

However, the following results in System.InvalidCastException: 'Specified cast is not valid.'

sx = (double)dt.Compute("StDev([x])", "x IS NOT NULL");

This would be highly convenient compared to a seperate method to calculate the standard deviation . And even at this point there is trouble when converting a column with null values into a List:

List<double> xValues = dt.Rows.OfType<DataRow>().Select(dr => dt.Field<double>("x")).ToList();

System.InvalidCastException: 'Cannot cast DBNull.Value to type 'System.Double'. Please use a nullable type.'

Any suggestions on handling this situation be it with null-conditionals or suggestions on the filter are highly appreciated.

rjw
  • 1
  • 1
  • Have you tried using a nullable type as the error suggests? `dt.Field` If you don't want the `null` values, you could add a `.Where(field => field != null)` before the `.ToList()` – Rufus L Nov 03 '20 at 18:13
  • Using `dt.Field` as a workaround to generate a list certainly works - ty. Still looking for a solution/ explanation regarding the `dt.Compute()` – rjw Nov 04 '20 at 12:18
  • They are the same, I made an edit. Thanks for pointing out. – rjw Nov 04 '20 at 15:51
  • Not sure why that would happen, but according to the Remarks section of the [STDEV (Transact-SQL) documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/stdev-transact-sql?view=sql-server-ver15), *"Null values are ignored"*. Have you tried just using an empty string for the filter? – Rufus L Nov 04 '20 at 15:52
  • `sx = (double)dt.Compute("StDev([x])", "");` leads to `System.InvalidCastException: 'Specified cast is not valid.'` – rjw Nov 05 '20 at 08:49

0 Answers0