C# 如何在 EF 查询中执行日期比较？

How do I perform Date Comparison in EF query?

提问by

Please help. I am trying to figure out how to use DATE or DATETIME for comparison in a linq query.

请帮忙。我想弄清楚如何在 linq 查询中使用 DATE 或 DATETIME 进行比较。

Example: If I wanted all Employee names for those who started before today, I would do something like this in SQL:

示例：如果我想要今天之前开始的员工的所有员工姓名，我会在 SQL 中执行以下操作：

SELECT EmployeeNameColumn
FROM EmployeeTable
WHERE StartDateColumn.Date <= GETDATE() //Today

But what about linq?

但是 linq 呢？

DateTime startDT = //Today

var EmployeeName =  
from e in db.employee
where e.StartDateColumn <= startDT 

The above WHERE doesn't work:

上面的 WHERE 不起作用：

Exception Details: System.NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

异常详细信息：System.NotSupportedException：LINQ to Entities 不支持指定的类型成员“Date”。仅支持初始值设定项、实体成员和实体导航属性。

采纳答案by jason

That should work. Are you sure that there isn't another part of the query that triggered the exception? I have several instances of queries of the form

那应该工作。您确定查询的其他部分没有触发异常吗？我有几个表单查询实例

var query = from e in db.MyTable
            where e.AsOfDate <= DateTime.Now.Date
            select e;

in my code.

在我的代码中。

回答by dxh

I'm curious to the error message saying 'Date', when you're passing a 'DateTime'. Could it be that 'StartDateColumn'is actually a 'Date', rather than a 'DateTime'in the database? That might mess up the comparison...

我很好奇错误消息说'Date'，当你传递一个'DateTime'. 难道这'StartDateColumn'实际上是 a 'Date'，而不是'DateTime'数据库中的 a ？这可能会搞乱比较......

回答by Shiraz Bhaiji

It may be due to the date in the database being nullable. Try this:

这可能是由于数据库中的日期可以为空。尝试这个：

var EmployeeName =
from e in db.employee
where e.StartDateColumn.Value <= startDT 

回答by Shiraz Bhaiji

You can not use .Date

你不能使用 .Date

If you would like to check for today you can create a datetime with no time

如果您想检查今天，您可以创建一个没有时间的日期时间

DateTime myDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
var e = (from mds in myEntities.Table
         where mds.CreateDateTime >= myDate
         select mds).FirstOrDefault();

回答by Yousuf Qureshi

try this:

尝试这个：

DateTime dd = DateTime.Parse("08/13/2010 00:00:00");
var data = from n in ContributionEligibilities
           where n.ModifiedDateTime.Date >= DateTime.Parse("08/13/2010").Date
           select n; 
data.Dump("Result") ;

回答by Les

I am using a LinqDataSource and I had problems getting my query with a Date Comparison in it to execute without errors. The answer is to use the WhereAddParameters function and add the test value as a strongly typed parameter.

我正在使用 LinqDataSource 并且我在获取带有日期比较的查询以无错误执行时遇到了问题。答案是使用 WhereAddParameters 函数并将测试值添加为强类型参数。

See the example below where I am matching a groupid and checking to see if the StopDate in my record is greater that or equal to a Date/Time stamp of now.

请参阅下面的示例，其中我匹配 groupid 并检查记录中的 StopDate 是否大于或等于现在的日期/时间戳记。

I am using this code fragment currently and it works like a charm.

我目前正在使用这个代码片段，它的作用就像一个魅力。

LinqCampaigns.WhereParameters.Add("StopDate", System.Data.DbType.Date, DateTime.Now.ToString())
LinqCampaigns.Where = "GroupId = " & myGrp & " &&  " & "StopDate >= @StopDate"

Works like a charm....

奇迹般有效....

回答by Niraj

use a local variable to store the Date value and then use that variable in the query:

使用局部变量来存储日期值，然后在查询中使用该变量：

DateTime today = DateTime.Now.Date; from scheme in context.schemes where scheme.EndDate > today select scheme

DateTime today = DateTime.Now.Date; from scheme in context.schemes where scheme.EndDate > today select scheme

回答by Ejaz

You can check the condition like this

您可以像这样检查条件

var nextDay = DateTime.Today.AddDays(1);

var query = from e in db.MyTable
            where e.AsOfDate >= DateTime.Today && e.AsOfDate < nextDay 
            select e;

here you'll get the records on AsOfDate date as we checking between today(00:00:00) and tommorow(00:00:00) we'll get today's date record only what ever may be the time...

在这里，当我们在今天（00:00:00）和明天（00:00:00）之间检查时，您将获得 AsOfDate 日期的记录，我们将仅获得今天的日期记录，可能是什么时间...

回答by ADBatBWD

.Date did not work, but .Daydid for me.

.Date 没有用，但.Day对我有用。

var query = from o in Payments
    where o.Order.OrderDate.Day != o.PaymentDate.Day
    orderby o.Order.OrderDate
    select new
    {
     o.Order.OrderID,
     o.Order.OrderDate,
     o.PaymentDate,      
     o.Order.FirstName,
     o.Order.LastName,
     o.Order.CustomerID
    };


query.Dump();

回答by Mandeep Janjua

Use the class DbFunctionsfor trimming the time portion.

使用该类DbFunctions来修剪时间部分。

using System.Data.Entity;

var bla = (from log in context.Contacts
           where DbFunctions.TruncateTime(log.ModifiedDate) 
                              ==  DbFunctions.TruncateTime(today.Date)
           select log).FirstOrDefault();

Source: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/

来源：http: //social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/