Comparing the dates logic in Linq

问题:

I want to query the result

public resultType GetData(int ClientID, DateTime CreatedDate)
{
    var Row = DB.tblPlans
                .Where(m => m.fkClient_Id == ClientID && m.PlanDate <= CreatedDate)
                .OrderByDescending(m => m.Id)
                .FirstOrDefault();
}

But the results are not coming as required because the "m.PlanDate" in the query is comparing the time also. so The parameter "CreatedDate" in the above function will always get "12:00:00 AM" attached to it suppose "2/17/2014 12:00:00 AM" because it is a datepicker control and I am converting it into datetime but the value of created date with which I have to compare it is having original timing suppose "2/17/2014 11:58:35 AM"

and that is why I am not getting the desired result.

I think the result will be fine if time portions get removed.

I have searched the above problem and it have many solutions like:: using AddDays(1); but I am not sure about it's working. also I tried:: http://www.codeproject.com/Articles/499987/LINQ-query-to-compare-only-date-part-of-DateTime

Please suggest me a better way to do this as this is not the first time I am getting this problem.

回答1:

If you are using Linq to SQL (as you specified in question tag), then simply get Date part of DateTime:

var Row = DB.tblPlans
            .Where(m => m.fkClient_Id == ClientID && m.PlanDate.Date <= CreatedDate)
            .OrderByDescending(m => m.Id)
            .FirstOrDefault();

With Entity Framework you should use EntityFunctions.TruncateTime(m.PlanDate)

回答2:

Have you tried this:

public resultType GetData(int ClientID, DateTime CreatedDate)
{
    var Row = DB.tblPlans
                .Where(m => m.fkClient_Id == ClientID && m.PlanDate.Date <= CreatedDate)
                .OrderByDescending(m => m.Id)
                .FirstOrDefault();
}

回答3:

The recommended way to compare dates in linq queries if you are using EntityFramework 6 is DbFunctions.TruncateTime(m.PlanDate) and for previous versions EntityFunctions.TruncateTime(m.PlanDate)