CODEWITHSUNDEEP
c#.netlinq
jrubengb
jrubengb

Reputation: 363

Linq query: append column to query results

I am trying to figure out how to append a column to Linq query results based on the max value of the query. Essentially, I want to create an EnumerableRowCollection of DataRows that would include a max value record with the same value for each record. So if i have a hundred records returned through the query, I want to next calculate the max value of one of the fields, then append that max value to the original query table:

DataTable dt = new DataTable();
dt = myDataSet.myDataTable;

EnumerableRowCollection<DataRow> qrySelectRecords =
          (from d in dt.AsEnumerable()
           where d.Field<DateTime>("readingDate") >= startDate && g.Field<DateTime>("readingDate") <= endDate
           select d);

Here's where I need help:

double maxValue = qrySelectRecords.Field<double>("field1").Max();

foreach (DataRow dr in qrySelectRecords)
{
 qrySelectRecords.Column.Append(maxValue)
}

Upvotes: 1

Views: 3152

Answers (1)

KeithS
KeithS

Reputation: 71565

Couple points, first: new DataTable() is redundant; you're instantiating a rather expensive object that will never be used because you're overwriting the reference in the very next line. You should consider removing the initialization and then joining the declaration and the actual assignment on one line.

Getting to the real question. DataRows cannot have columns added to them directly; you have to add the column to the entire DataTable containing those rows. Once that's done, just set the value. As long as you don't need the operation translated into an external query language like SQL, you can inline this operation using a monadic extension method:

DataTable dt = myDataSet.myDataTable;
dt.Columns.Add("MaxField1");

EnumerableRowCollection<DataRow> qrySelectRecords =
          (from d in dt.Rows().AsEnumerable().OfType<DataRow>()
           where d.Field<DateTime>("readingDate") >= startDate 
              && d.Field<DateTime>("readingDate") <= endDate
           let m = dt.AsEnumerable().Max(dr=>dr.Field<double>("field1"))
           select d.WithColumnSet("MaxField1", m));

...

public static DataRow WithColumnSet(this DataRow input, string columnName, object value)
{
    input[columnName] = value;
    return input;
}

Upvotes: 2

Related Questions