How to get records between two dates in C#, ASP.NET MVC 3 using Entity Framework?

Question

I am not able to get records between two dates.

I tried using a normal query, but I am not getting any results:

 log.Info("Parameters Received...");
 log.Info("name:"+param1); // => "SELECT"
 log.Info("city:"+param2); // => "SELECT"
 log.Info("userId :"+userId); // =>""
 log.Info("creationDateFrom :"+creationDateFrom); // => 10/07/2018
 log.Info("creationDateTo :"+creationDateTo); // => 12/07/2018     

var report = reportDBObj.tbl_reports.AsQueryable();

if (param1 != null && !param1.Equals("SELECT")) 
     report = report.Where(x => x.name == param1);

if (param2 != null && !param2.Equals("SELECT")) 
     report = report.Where(x => x.city == param2);

if (userId != null && userId != "") 
     report = report.Where(x => x.user_id == userId);

Boolean dateCheck = false;
DateTime createDateFrom = DateTime.MinValue, createDateTo = DateTime.MinValue;

if (!string.IsNullOrEmpty(creationDateFrom))
{
    // createDateFrom = Convert.ToDateTime(creationDateFrom);
    createDateFrom = DateTime.Parse(creationDateFrom);
    dateCheck = true;
}

if (!string.IsNullOrEmpty(creationDateTo))
{
    // createDateTo = Convert.ToDateTime(creationDateTo);
    createDateTo = DateTime.Parse(creationDateTo);

    if (!dateCheck)
        dateCheck = true;
}

if (dateCheck)
{
    if (createDateFrom == DateTime.MinValue)
        createDateFrom = DateTime.Now;
    else if (createDateTo == DateTime.MinValue)
        createDateTo = DateTime.Now;
}

if (dateCheck)
{
    Debug.WriteLine("createDateFrom :" + createDateFrom);
    Debug.WriteLine("createDateTo :" + createDateTo);

    report = report.Where(x => x.creation_date >= createDateFrom && x.creation_date <= createDateTo); // this line returns no records
}

repListTbl = report.OrderByDescending(x => x.creation_date).ToList();

What's wrong with my code?

Thanks

Answer 1

Two ways to do so.

First with two WHERE clause:

var result = context.EntityName
             .Where(entry => entry.DateField >= StarDate.SelectedDate.Value)
             .Where(entry => entry.DateField <= EndDate.SelectedDate.Value).ToList();

Second with one WHERE clause and MIN MAX:

    var result = context.YourEntityName.Where(entry => entry.DateField >= minDate 
                 && entry.DateField <= maxDate).ToList();

If non of this fetches any results, check your DB for the date format of the columns and that the records do indeed exist.