Comparing Dates in LINQPad

kennyp

I am very new to LINQ and LINQPad (1 week). I have an SQL database that has a string date, testdate, in the format "yyyymmdd". I add a column that is a real date as in the following:

var allProcessed=
from q in ABGDEMOG_2s
where q.Processed==true
select new {q.CLINIC_NUM,q.Last_Name,q.TestDate,q.TestTime,RealDate=DateTime.Parse(q.TestDate.Substring(4,2)+"/"+q.TestDate.Substring(6,2)+"/"+q.TestDate.Substring(0,4))};

allProcessed.Count().Dump("Total number of Processed Records");

This works normally. If I perform a dump of the records, the RealDate column appears and is of type DateTime.
However, the problem arises when I try to get a subset of the processed records according to date:

var monthProcessed=
from m in allProcessed
where m.RealDate >= new DateTime(2012,05,01)
select m;

monthProcessed.Count().Dump("Total number of Processed Records in May 2012");

The error message is always the same:

Method 'System.DateTime Parse(System.String)' has no supported translation to SQL.

No matter how I try to create a date for compare in monthProcessed, I always get the error message above. Am I missing something?

kennyp

However, the following does work!

SqlCommand cmdBlood=new SqlCommand("SELECT DISTINCT * FROM ABGDEMOG_2 WHERE Processed=1",cnxnBlood);
cmdBlood.CommandType=CommandType.Text;

DataColumn newcolumn=new DataColumn("RealDate",typeof(DateTime),"substring(testdate,5,2)+'/'+substring(testdate,7,2)+'/'+substring(testdate,1,4)");
newcolumn.AutoIncrement=false;
newcolumn.ReadOnly=true;

SqlDataAdapter daBlood = new SqlDataAdapter(cmdBlood);
DataSet ds = new DataSet();
daBlood.Fill(ds,"DemogData");
DataTable tblBloodDemog=ds.Tables["DemogData"];
tblBloodDemog.Columns.Add(newcolumn);

tblBloodDemog.Rows.Count.Dump("Total Number of Processed Records");

var a =
from b in tblBloodDemog.AsEnumerable()
where b.Field("RealDate") >= DateTime.Parse("2012-05-01")
where b.Field("RealDate") <= DateTime.Parse("2012-05-31")
select b;

a.Count().Dump("Total Number of Processed Records in May 2012" );

JoeAlbahari

Your second solution retrieves all rows from the database (where processed is 1) and does the date filtering locally.

If you're happy with this, you can achieve the same effect with LINQ with .AsEnumerable(), which forces the query to execute locally from that point on:

var monthProcessed=
from m in allProcessed.AsEnumerable()
where m.RealDate >= new DateTime(2012,05,01)
select m;

kennyp

Thanks so much Joe! As I said, I haven't been using LINQ or LINQPad very long and the nuances are not yet second nature. By the way, I must say I am extremely impressed with the LINQPad product and I, for one, do not miss Query Analyzer or SQL only analyzer products at all. I bought the "finisher" add on within 2 days of using LINQPad and it is worth every penny.

The second solution I presented will be used to count total procedures performed during a designated time period. Again, I have to say that LINQPad and LINQ will be speeding up develop on the "drop in" projects that land in my lap far too frequently. One of the physicians whose office is near mine was looking over my shoulder and we started two research projects based on the rapidity with which I was able to answer his questions. Good job and thanks again for the information on using .AsEnumerable() on the local level.