在LINQ Query中将字符串转换为datetime并检查今天的日期(In LINQ Query convert the string to datetime and check with toda

在LINQ Query中将字符串转换为datetime并检查今天的日期(In LINQ Query convert the string to datetime and check with today's date)

在表中，DueDate数据类型是varchar。 现在我想查看今天的截止日期

var query = (from o in db.Order_Reports where Convert.ToDateTime(o.ReportDueDateTime) >= DateTime.Now select o);

错误

LINQ to Entities无法识别方法'System.DateTime ToDateTime（System.String）'方法，并且此方法无法转换为商店表达式。

我想如何将字符串转换为日期和时间并检查今天的日期

In Table, DueDate data type is varchar. Now I want to check the due date with today's date

var query = (from o in db.Order_Reports where Convert.ToDateTime(o.ReportDueDateTime) >= DateTime.Now select o);

The error

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.

How am i suppose to convert the string to date and time and check with today's date

最满意答案

我不知道如你所描述的那样实现它的方法，因为

SQL中没有函数可以通过不等式过滤字符串格式的日期（至少那些与日期格式一起使用并且可以从LINQ转换为SQL查询的日期）。 您的数据库设计存在缺陷，导致“正常”方法无法正常工作

但是，有一些可能的解决方法

修改数据库，使日期为实际日期时间格式（最佳选项，但当然有时不可用） 创建存储过程以实现相同的目标。 在SQL代码中，您可以使用日期解析 查询修改如下：

（码）

// Our required date DateTime reportDate = new DateTime(2014,1,1).Date; // Let's find number of days between now and required day. Ensure that the date is not in the future! int deltaDays = (DateTime.Now.Date - date).Days; // Let's get the list of dates which we need the reports for var dates = Enumerable.Range(0, deltaDays + 1).Select(dd => DateTime.Now.Date.AddDays(-dd).ToString("MM/dd/yyyy")).ToArray(); // and query by this list var query = (from o in db.Order_Reports where o.ReportDueDateTime in dates select o);

这样效率会有点低，但可以在不更改数据库的情况下达到目的。 将其视为临时解决方案。

I don't know a way to implement it as you describe it, because

There's no function in SQL that will filter the dates in string format by inequality (at least those that work with your date format and are convertable from LINQ to SQL query). You have a flaw in your database design which prevents "normal" methods from working

However, there are some possible workarounds

Amend the database so that the date is of actual datetime format (best option, but of course sometimes unavailable) Create a stored procedure to achieve the same goal. In the SQL code, you can use date parsing Amend the query as follows:

(code)

// Our required date DateTime reportDate = new DateTime(2014,1,1).Date; // Let's find number of days between now and required day. Ensure that the date is not in the future! int deltaDays = (DateTime.Now.Date - date).Days; // Let's get the list of dates which we need the reports for var dates = Enumerable.Range(0, deltaDays + 1).Select(dd => DateTime.Now.Date.AddDays(-dd).ToString("MM/dd/yyyy")).ToArray(); // and query by this list var query = (from o in db.Order_Reports where o.ReportDueDateTime in dates select o);

This will be a little inefficient, but achieve the purpose without changing the DB. Treat it as temporary solution.