LINQ Query or stored procedure to return max value in a stated column

Question

Table like

datetime          a1   b1   x2 ...  
07-01-2009 13:10   8    9    10  
07-01-2009 13:11   8    8    2  
07-01-2009 13:12   9    1    1

1 row per second for a whole day (=86400 rows); ~40 columns; all same format
I'm looking for a way to retrieve a max value and the time for a column to specify.

I'm looking for a way to retrive a max value and the according time for a column to specify within a timeframe.

Something like

Select top 1 time,a1 from table
    where (datetime>=begin and datetime<end) 
    order by a1 desc

will work but I cannot use the column as a parameter.

A LINQ solution or a SP would be great.

Do I have to worry about performance when sorting a whole dataset to retrieve a max value? Maybe MAX function will be faster.

UPDATE

I tried to implement it the dynamic linq way, like Timothy (tvanfossen) suggested

Dim q2 = context.table _
  .Where("t >= @0 AND t < @1", begin, end) _
  .OrderBy("@0 desc", col) _
  .Take(1) _
  .Select(col)

BUT this returns the first value in the table. This returns the first value in the time frame and not the max value. Looking at the SQL profiler I see that there is no ORDER clause.
Any idea?

UPDATE 2
For some reason the substitution value does not work in the orderby clause.
.OrderBY (col + " desc") works

Answer 1

If you want the column name to be dynamic, you might want to use Dynamic Linq from the VS2008 code samples. Then you can specify the name of the column to sort by.

var query = context.table
                   .Where( t = t.begin <= date && date < t.end )
                   .OrderBy( "a1 desc" )
                   .Take(1)
                   .SingleOrDefault();

Answer 2

In SQL:

select max(a1)
from table
where (datetime>=begin and datetime<end)

You don't need to sort, just use the standard aggregate function. To be able to choose the column dynamically you need to create the SQL dynamically, with string concatenation but be very careful to ensure the column name is really a column name, and not SQL injection.

In LINQ, again there is an aggregate to use:

var res = datacontext.Table
                     .Where(t => t.datetime >= begin && t.datetime < end)
                     .Max(t => t.a1);

The lambda expression passed to Max selecting the column to get the maximum value of. To handle the dynamically selected column there are two routes:

First, you could build the expression in pieces, good if there is a small fixed set of columns:

Expression<Func<TableType, ColumnType>> colSelector = null;
switch (column) {
  case "a1":
    colSelector = t => t.a1;
    break;
  case "b2":
    colSelector = t => t.b2;
    break;
  ...
}
var res = datacontext.Table
                     .Where(t => t.datetime >= begin && t.datetime < end)
                     .Max(colSelector);

Or, second option: build the Expression yourself with the expression API. See here for details: http://www.albahari.com/nutshell/predicatebuilder.aspx

Answer 3

I think this will work. Basically use a function to return the column to order by.

var result = Table
                .OrderByDescending( row => GetColumnOfInterest(row) )
                .First()
                .dateTime;

int GetColumnOfInterest( Row row )
{
   if ( ... )
   {
      return row.a1;
   }
   else if ( ... )
   {
      return row.b1;
   }
}

Answer 4

How about:

IEnumerable<Table> results = Table;

switch ( condition )
{
    condition 1:
        results = results.OrderByDescending( row => row.a1 );
    condition 2:
        results = results.OrderByDescending( row => row.a2 );
    condition 3:
        results = results.OrderByDescending( row => row.a3 );

    ....

}

var result = results.First().dateTime;