How to write a Linq 'Where' clause for nullable datetime?

miss786

Dear all, I am writing to seek help in writing a linq 'Where' clause to get results between two dates. I am using the following method below, which is currently showing an empty string.

    \[HttpGet\]
    public IEnumerable GetDate(DateTime? start, DateTime? end)
    {

        var data = from c in db.database\_WICs
                   where c.UploadDate == start &&
                         c.UploadDate == end
                   select c;
        return data.ToList();
    }

http://localhost:45361/api/data?start=25/11/2013&end=28/11/2013 Any help would be very much appreciated. Many thanks

cdpsource

try this

[HttpGet]
public IEnumerable GetDate(DateTime? start, DateTime? end)
{

        var data = from c in db.database\_WICs
                   where c.UploadDate == (start==null? c.UploadDate : start) &&
                         c.UploadDate == (end=null? c.UploadDate : end)
                   select c;
        return data.ToList();
    }

Richard Deeming

miss786 wrote:

get results between two dates

miss786 wrote:

c.UploadDate == start && c.UploadDate == end

The query you've written will only return records where the UploadDate is equal to the start parameter AND equal to the end parameter. Unless you pass the same value for both parameters, this condition will never be met. Try something like this:

var data = db.databases_WICs;

if (start != null)
{
data = data.Where(c => c.UploadDate >= start);
}
if (end != null)
{
data = data.Where(c => c.UploadDate <= end);
}

return data.ToList();

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

miss786

Thank you so much for your response. I tried your code and I am sorry to inform, I got the following error "Cannot convert null to 'bool' because it is a non-nullable value type" Any help would be very much appreciated.

miss786

I really appreciate your time and help. I am currently experiencing error: Cannot implicitly convert type:'System.Linq.IQueryable' to 'System.Data.Entity.DbSet'. An explicit conversion exists (are you missing a cast?) on the following lines of code:

if (start != null)
{
data = data.Where(c => c.UploadDate >= start);
}
if (end != null)
{
data = data.Where(c => c.UploadDate <= end);
}

I tried adding FirstOrDefault() to the code but I am sorry to inform that I can not figure out the reason for the issue above. Thank you

Richard Deeming

Try either:

var data = db.databases_WICs.AsQueryable();

or:

IQueryable<database_WICs> data = db.databases_WICs;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

miss786

Thanks Richard once again, you have been a great help. I manage to get the issue resolved and the problem was in date converter code that was in the Global.asax file which was not allowing the date parameters to pass through the URL query string. Thank you so much for your help and time into this issue. I really appreciate it.