Casting String as DateTime in LINQ

Question

I have a table with a following format.

PID     ID       Label        Value
------------------------------------------
1       1        First Name    Jenna
1       2        DOB           10/12/1980

I need to retrieve all PIDs where First name starting with J and Month of DOB is 10.

in my code, I retrieve these in DataTable in C# and then tried to use LINQ to retrieve the results I want. This is just an example. These Labels could be anything user defines.

using LINQ I am able to retrieve all PIDs where First Name start with J, but every time I tried to Cast Value for DOB I get cast not valid error. I cannot change the column type in the database since Value could contain any type of information.

Here's a piece of my code. I am new to LINQ, and still trying to figure out around it.

var resultQuery = from r in query.AsEnumerable()
where (r.Field<string>("Label") == Label  &&
r.Field<DateTime>("Value").Month == 10)
select r.Field<int>("PID");

Answer 1

Since not all items in the Value column of the table are convertible to DateTime, what you have will fail on invalid conversions. You can add in a clause that first checks that the value is a DateTime and only if it is, converts it and checks the .Month property.

DateTime d;
var resultQuery = from r in query.AsEnumerable()
                  where (r.Field<string>("Label") == Label &&
                      DateTime.TryParse(r.Field<string>("Value"), out d) && 
                      d.Month == 10)
                  select r.Field<int>("PID");

To potentially improve readability, you could also extract this out into a separate method:

var resultQuery = from r in query.AsEnumerable()
                  let d = TryGetDate(r.Field<string>("Value"))
                  where (r.Field<string>("Label") == Label &&
                      d != null && 
                      d.Month == 10)
                  select r.Field<int>("PID");

private DateTime? TryGetDate(string value)
{
    DateTime d;
    return DateTime.TryParse(value, out d) ? d : default(DateTime?);
}

Answer 2

You are going to end up filtering in memory which isn't very efficient.

So first select your data

var data= from r in query.AsEnumerable();

Then filter on the data

var filtered = from item in data
               where item.Label == "Label"
               && Convert.ToDateTime(item.DOB).Month == 10
               select item.PID;