Looking for Specific Date

Question

Anyone know how to query for a specific date within entity framework ? I tried following code, but it gives me NotSupportedException.

var deposit = (from tempDeposit in entities.Deposit
where !tempDeposit.IsApproved
&& tempDeposit.CreatedDate.Date == DateTime.Today
select tempDeposit).FirstOrDefault();

I also tried following code but still gave me NotSupportedException.

var deposit = (from tempDeposit in entities.Deposit
where !tempDeposit.IsApproved
&& tempDeposit.CreatedDate.ToFullString() == DateTime.Today.ToFullString()
select tempDeposit).FirstOrDefault();

ToFullString() is an extension method

public static string ToFullString(this DateTime date){
  return date.ToString("yyyyMMdd");
}

Please help.

Answer 1

Try this:

var d1 = DateTime.Today;
var d2 = d1.AddDays(1);
var deposit = (from tempDeposit in entities.Deposit
               where !tempDeposit.IsApproved
                     && tempDeposit.CreatedDate >= d1
                     && tempDeposit.CreatedDate < d2
               select tempDeposit).FirstOrDefault();

Since you can't use the .Date property of a DateTime column, since that would have to be written in SQL with a range of converts, casts, truncates, etc. then you need to compare the whole DateTime value, date and time both, to a value, and thus you need to compare to a range.

Edit Changed to reflect that .AddDays isn't supported.

Answer 2

Well, after sometime tried, I found the solution

var deposit = entities1.Deposit
              .Where("CreatedDate >= @0 and CreatedDate < @1", DateTime.Today, DateTime.Today.AddDays(1))
              .FirstOrDefault();