动态LINQ-使用GROUP BY/SELECT语法进行挣扎 [英] Dynamic LINQ - Struggling with GROUP BY / SELECT Syntax

问题描述

我有一组重复的数据，这些数据是我从多个SQL表(长话大说，尽管它们具有相同的数据，但它们都有不同的名称)检索到.NET DataTable中的信息:-

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

我想使用 LINQ 对日期/时间字段上的数据进行分组，以便我最终得到如下记录:-

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

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

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

我的问题是我不知道会有多少数据集(示例中有三组，但可以有任何数量)，所以我需要使用动态LINQ.

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.

对于固定数量的字段，我可以进行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)
                        });

但是我一直在努力使用System.Linq.Dynamic使它动态化，以下内容给我一个错误:-

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)");

• 错误是:-

System.Linq.Dynamic.ParseException{不存在适用的聚合方法'sum'"}

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

完成GroupBy后，我只是想不通如何引用"Point_Value"字段-根据数据集的数量，将有多个"sum(Point_ValueX)"字段，但我可以.甚至现在都无法在单个领域使用它！

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 !

非常感谢，

大卫.

推荐答案

我认为您无法通过这种方式完成它.动态linq似乎不具备解析包含索引器的表达式的能力.

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.

但是，您可以结合使用动态LINQ和常规LINQ:

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"))});

主要的缺点是将 GroupBy 结果转换为 IEnumerable< IGrouping< DynamicClass，DataRow>> .

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

这篇关于动态LINQ-使用GROUP BY/SELECT语法进行挣扎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！