No applicable method 'Select' exists in type 'DataRow'

Question

I have a data table result and I converted it to Enumerable using AsEnumerable extension.

var dataTableToEnumerable = dataTable.AsEnumerable();

Now Im doing a groupBy on it using Linq Dynamic Extensions and it works fine but I cant process Select(),Sum(),Min() etc on the aggregrated result.

But methods like Count(),First(),FirstOrDefault() exist.

dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null&&it[\"price\"] != null)")
.GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)", "it").Select("it.Sum(\"price\")").

Also with reference to this question I changed my query to this:-

(dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null&&it[\"price\"] != null)")
.GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)", "it") as IEnumerable<IGrouping<DynamicClass,DataRow>>).Select("it.Sum(\"price\")").

But Now its showing No Method Select exist on IEnumerable<IGrouping<DynamicClass,DataRow>>

Its giving the error No applicable method 'Select' exists in type 'DataRow', Can Any one help on this?

#Update1

I have tried with that first Answer. It's working fine, but how can i change it there is multiple field selection after groupby? this was my query : - dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null && it[\"price\"] != null)") .GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)", "it") .Select("new(it.Select(it[\"price\"] as price,it[\"cost\"] as cost)")

The result set is correct but,
.AsEnumerable() .Select(pg => (((IEnumerable<object>)pg).Sum(p => (double)p)));

this is causing error

Unable to cast object of type '<>f__AnonymousType15`2[System.Object,System.Object]' to type 'System.IConvertible'.

Answer 1

You can extract the price column using the DataRow string indexer method as an object and then convert from Dynamic LINQ to LINQ to Objects using AsEnumerable, which gets you IEnumerable<object> which you can then cast to the appropriate type:

var ans = dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null && it[\"price\"] != null)")
            .GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)", "it")
            .Select("it.Select(it[\"price\"])")
            .AsEnumerable()
            .Select(pg => (((IEnumerable<object>)pg).Sum(p => (double)p)));

If you don't need any other fields from the GroupBy, you can just group the price column:

var ans = dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null && it[\"price\"] != null)")
            .GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)", "it[\"price\"]")
            .AsEnumerable()
            .Select(pg => (((IEnumerable<object>)pg).Sum(p => (double)p)));

To handle more than one column, you must manually call the extension method Sum because extension methods don't work with dynamic objects, and you must manually cast the lambdas to Func since the compiler can't tell if you need an expression tree or function in a dynamic expression:

var ans = dataTableToEnumerable.AsQueryable().Where("(it[\"yoko\"] != null && it[\"price\"] != null)")
            .GroupBy("new(it[\"proposalid\"] as proposalid,it[\"yoko\"] as yoko)")
            .Select("it.Select(new(it[\"price\"] as price, it[\"cost\"] as cost))")
            //.AsEnumerable()
            //.Select(pg => (((IEnumerable<object>)pg).Sum(p => (double)p)))
            .AsEnumerable()
            .Select(x => new {
                sumPrice = Enumerable.Sum(x, (Func<dynamic, double>)(y => y.price)),
                sumCost = Enumerable.Sum(x, (Func<dynamic, double>)(y => y.cost))
            })
            .ToList();