将日期与字符串实体框架进行比较 [英] Comparing date with string Entity Framework
问题描述
假设我有一个SQL Server数据库和一个表,如下所示:
Lets assume that I have a SQL Server database and a table which looks like:
Id int NOT NULL,
Date date NOT NULL
我有相应的实体框架模型:
I have corresponding entity framework model:
public class Bundle
{
public int Id {get; set;}
public DateTime Date {get; set;}
}
用户可以键入一个可以是任何东西的字符串。我需要找到所有项目,其中Date或日期的任何部分包含用户输入的字符串。所以基本上我需要执行查询:
User can type a string that can be anything. I need to find all items where Date, or any part of date, contains string entered by user. So, basically, I need to perform query:
SELECT Id, Date
FROM Bundles
WHERE Date LIKE '%user_query_here%'
我的第一次尝试是
query.Where(b => b.Date.ToShortDateString().Contains(filter.Date))
这会抛出一个NotSupportedException,所以我试过这个:
This throws a NotSupportedException, so I tried this:
query.Where(b => Convert.ToString(b.Date).Contains(filter.Date));
请注意,这个 filter.Date 是字符串。它不是一个DateTime结构。
Please note, that filter.Date is string. Its not a DateTime structure.
这也引发了一个异常。所以我的问题是如何执行上面的查询?
PS:我不能在内存中执行过滤,这个表有数千行。
This also throws an exception. So my question is how execute query written above?
PS: I can't perform filtering in memory, this table has many thousands of rows.
推荐答案
可悲的是,没有简单的方式将datetime转换为l2e的字符串。
There's no simple way to convert a datetime to string with l2e, sadly.
您可以使用一些 SqlFunctions 方法
SqlFunctions.DatePart
将返回表示日期(年,月,日等)的一部分的整数。
will return an int representing a part of the Date (year, month, day for example).
和
SqlFunctions.StringConvert
可以帮助您转换字符串中的int(将其转换为双倍),然后连接。
can help you to convert the int in a string (casting it to double first), which you can then concatenate.
像(b =>(SqlFunctions.StringConvert((double))SqlFunctions.DatePart(y,b))
Something like
.Where(b => (SqlFunctions.StringConvert((double)SqlFunctions.DatePart("y", b))
+ SqlFunctions.StringConvert((double)SqlFunctions.DatePart("m", b))
//etc.
).Contains(filter.Date)
当然这是不可读的,真的不是索引友好的。
of course this is unreadable, and really not index-friendly.
更简单和更干净的方式,如果您使用 sql server ,则将创建并使用计算列。
A (much) easier and cleaner way, if you're working with sql server, would be to create and use a computed column.
您可以创建它(如果您使用代码优先和迁移,请找到如何正确执行此操作)
You can create it like that (google to find how to do this properly if you use Code First and Migrations)
alter table add StringDate as convert(varchar, [Date], 112)//or another format, this will be yyyymmdd
如果您首先使用代码,则必须使用属性
If you use code first, you'll have to mark the property with attribute
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
然后你将能够做到
.Where(b => b.StringDate.Contains(filter.Date))
这篇关于将日期与字符串实体框架进行比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!