DGjuniordev DGjuniordev - 19 days ago 7
C# Question

Dynamic LINQ - Struggling with GROUP BY / SELECT Syntax

I have a repeating set of data that I'm retrieving from multiple SQL tables (long story but they all have different names despite having the same data) into a .NET DataTable:-

Point_Date -> Point_Value0 -> Point_Value1 -> Point_Value2 -> Point_ValueX
24/11/2014 16:18:07 -> 15.1 -> NULL -> NULL
24/11/2014 16:19:07 -> 15.2 -> NULL -> NULL
24/11/2014 16:20:07 -> 15.3 -> NULL -> NULL
24/11/2014 16:18:07 -> NULL -> 16.1 -> NULL
24/11/2014 16:19:07 -> NULL -> 16.2 -> NULL
24/11/2014 16:20:07 -> NULL -> 16.3 -> NULL
24/11/2014 16:18:07 -> NULL -> NULL -> 17.1
24/11/2014 16:19:07 -> NULL -> NULL -> 17.2
24/11/2014 16:20:07 -> NULL -> NULL -> 17.3


I want to group the data on the date/time field using LINQ so that I end up with records like:-

24/11/2014 16:18:07 -> 15.1 -> 16.1 -> 17.1

My problem is that I don't know how many sets of data there will be (there are three in the example but there could be any number) so I need to use dynamic LINQ.

I'm ok for the LINQ query for a fixed number of fields:-

var dtReport = (from row in dtPoints.AsEnumerable()
group row by row.Field<DateTime>("Point_Date")
into t
select new
{
TempDate = t.Key,
Value1 = (double?) t.Sum(r => r.Field<double?>("Point_Value0") ?? 0),
Value2 = (double?)t.Sum(r => r.Field<double?>("Point_Value1") ?? 0),
Value3 = (double?)t.Sum(r => r.Field<double?>("Point_Value2") ?? 0)
});


But I'm having a real struggle making it dynamic using System.Linq.Dynamic, the following gives me an error:-

var myRpt2 = dtPoints.AsEnumerable()
.AsQueryable()
.GroupBy("new ( it[\"Point_Date\"] as GrpByCol1)", "it")
.Select("new (it.key as TempDate, it.sum(\"Point_Value0\") as SumValue)");



  • the error being:-



System.Linq.Dynamic.ParseException
{"No applicable aggregate method 'sum' exists"}

I just can't figure out how to refer to the 'Point_Value' fields once I've done the GroupBy - there will be multiple 'sum(Point_ValueX)' fields depending on the number of sets of data but I can't even get it working for a single field at the moment !

Many Thanks,

David.

Answer

I don't think you can get it done that way. It seems that dynamic linq is not equipped to parse an expression containing an indexer.

You can however use a combination of dynamic LINQ and regular LINQ:

var myRpt2 = (
        dtPoints.AsEnumerable()
        .AsQueryable()
        .GroupBy("new ( it[\"Point_Date\"] as GrpByCol1)", "it")
        as IEnumerable<IGrouping<DynamicClass,DataRow>>
    )
    .Select (r => new { ((dynamic)r.Key).GrpByCol1, 
                        Sum = r.Sum(x => x.Field<decimal>("Point_Value0"))});

The main twist is the cast of the GroupBy result to IEnumerable<IGrouping<DynamicClass,DataRow>>.

Comments