We help IT Professionals succeed at work.

LINQ help

countrymeister
on
I have a datatable with two columns - a date field (datetime) and currency field(string)

I need to get the max date from the datatable where the date is less than some derived date
and the Currency matches a currency field

strCurrency = "USD"
dBusDate = '06/01/2010'

In this example I need to get the max date less than dBusDate and currency = USD from the datatable
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2015

Commented:
How about this?
static void Main(string[] args)
{
    DataTable t = new DataTable();
    DataRow r;

    t.Columns.Add("currency", typeof(string));
    t.Columns.Add("BusDate", typeof(DateTime));

    for (int i = 0; i < 10; i++)
    {
        r = t.NewRow();
        r["currency"] = "USD";
        r["BusDate"] = new DateTime(2010, 06, 01 + i);
        t.Rows.Add(r);
    }

    var result = from row in t.Rows.Cast<DataRow>()
                 group row by row["currency"].ToString() into g
                 select new { Maximum = g
                     .Where(x => Convert.ToDateTime(x["BusDate"]) < new DateTime(2010, 6, 4) && x["currency"].ToString() == "USD")
                     .Max(y => Convert.ToDateTime(y["BusDate"])) };

    foreach (var row in result)
    {
        Console.WriteLine("Max Date: {0}", row.Maximum);
    }

    Console.ReadKey();

}

Open in new window

Retired
Distinguished Expert 2017
Commented:
Hi countrymeister;

I think what you are looking for is something like this, see code snippet.

In the code snippet when you see something like this:

row.Field<DateTime?>("Date")

The <DateTime> is the DataType of the column in question and ("Date") is the name of the column in question.

Fernando
String strCurrency = "USD";
DateTime? dBusDate = DateTime.Parse("06/01/2010");

var result = (from row in table.Rows.Cast<DataRow>()
              where row.Field<String>("Currency") == strCurrency && row.Field<DateTime?>("Date").Value < dBusDate.Value
              orderby row.Field<DateTime?>("Date") descending
              select row.Field<DateTime?>("Date")).FirstOrDefault();

Console.WriteLine("Date is = {0}", (result.HasValue)? result.Value.ToShortDateString() : "Non Data Found" );

Open in new window

Author

Commented:
thanks
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Not a problem, glad I was able to help.  ;=)