How to use/pass date parameter in Linq query

Question

User-356787864 posted

How do I write the c# code for this line:

Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == "10/09/2013").Sum(r => (Double)r["CALLS"]);

Here I want to replace the "10/9/2013" with a variable value and "Calls" with a variable like this:

Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == myDate).Sum(r => (Double)r[sumBy]);

I tried this:

string ymd = "2013-10-09";    // this definitely will come from database column
System.DateTime myDate = Convert.ToDateTime(DateTime.Parse(ymd).ToString("M/dd/yyyy"));

var sum = dtCurrentDateRows.AsEnumerable()
              .Where(r => r.Field<System.DateTime>("DATE") == myDate) 
            .Sum(r => r.Field<Double>("CALLS"));

This gives error "Specified cast is not valid";

Answer 1

User-760709272 posted

System.DateTime myDate = Convert.ToDateTime(DateTime.Parse(ymd).ToString("M/dd/yyyy"));

// this line takes a string, converts it to a date, converts it to a string, converts it to a date
// just use either ToDateTime or the Parse or DateTime.TryParse.  Dates don't have internal formats,
// you can't convert a date from one format to another

string ymd = "2013-10-09";    // this definitely will come from database column
System.DateTime myDate;

DateTime.TryParse(ymd, out myDate);
 
var sum = dtCurrentDateRows.AsEnumerable()
               .Where(r => r.Field<System.DateTime>("DATE") == myDate)  
            .Sum(r => r.Field<Double>("CALLS"));

You have to watch for the "time" element though as your .net date has a time of 00:00:00 and if your SQL "DATE" column has a time of anything other than 00:00:00 then the comparison won't be valid.

Answer 2

User-356787864 posted

Even these lines gives me error: Specified cast is not valid.

This is the code:

 string ymd = "10/9/2013";
System.DateTime myDate;
DateTime.TryParse(ymd, out myDate);

var sum = dtCurrentDateRows.AsEnumerable()
                                      .Where(r => r.Field<System.DateTime>("DATE") == myDate)
                                      .Sum(r => r.Field<Double>("CALLS"));

Answer 3

User-760709272 posted

What SQL type is "DATE" and what type is "CALLS"?

Answer 4

User-356787864 posted

Date is string type and values in calls are Double.

Also when I write this :

 Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == "10/09/2013").Sum(r => (Double)r["CALLS"]);

It gives me correct values but replacing the hard code values gets me either error or 0.0

Answer 5

User-760709272 posted

If DATE is a string you can't cast it to DateTime which is what you are doing in your code.  You shouldn't store dates as strings, but that's a separate matter.

string ymd = "2013-10-09";    // this definitely will come from database column
System.DateTime myDate;
DateTime.TryParse(ymd, out myDate);
 
var sum = dtCurrentDateRows.AsEnumerable()
               .Where(r => (string)r["DATE"] == myDate.ToString("M/dd/yyyy"))  // use whatever format the date is stored in in your database