LINQ where子句为可空的datetime字段 [英] LINQ where clause for nullable datetime field

问题描述

我正在使用Searching，Paging开发MVC应用程序。我有UploadDate日历控件用于过滤目的与许多其他字段。这可能为null或可能包含日期。



如何编写LINQ where子句来检索其UploadDate（列名）等于表单上给定的UploadDate的记录。

I am developing MVC application with Searching, Paging. I have UploadDate calendar control for filter purpose with many other fields. This may be null or may contain date.

How to write LINQ where clause that retrieves records whose UploadDate (column name) equals to given UploadDate on form.

public ActionResult MultipleSearch(string searchByUserName, string searchByReaderName, int? searchByReaderType, string searchByUploadDate, int? page)
        {
            return View(db.TestUploadData2.Where(a =>
                (String.IsNullOrEmpty(searchByUserName) || a.UserName.StartsWith(searchByUserName)) &&
                (String.IsNullOrEmpty(searchByReaderName) || a.ReaderName.StartsWith(searchByReaderName)) &&
                (String.IsNullOrEmpty(searchByUploadDate) || a.UploadDate.ToShortDateString() == searchByUploadDate)                 
                ).Where(var => var.ReaderTypeId == searchByReaderType || var.ReaderTypeId > 0).ToList().ToPagedList(page ?? 1, 100));
        }

使用这种方法，将错误称为

With this approach, getting error as "

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

替换

"

Replacing

(String.IsNullOrEmpty(searchByUploadDate) || a.UploadDate.ToShortDateString() == searchByUploadDate )

使用

With

(String.IsNullOrEmpty(searchByUploadDate) || DateTime.Compare(a.UploadDate, Convert.ToDateTime(searchByUploadDate)) == 0)

给出错误

gives error as

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

请给出建议。



我尝试过：



谷歌搜索了很多这个问题，但找不到合适的解决方案。请注意，searchByUploadDate参数可以为null或者可以包含日期...

Please give suggestion.

What I have tried:

Googled a lot for this issue but can't find suitable solution. Please note that searchByUploadDate parameter may be null or may contain date...

推荐答案

在将日期值转换为查询之前，将其转换为C＃中的日期值：

Convert the date value in C#, before passing it to the query:

DateTime uploadDate;
bool haveUploadDate = !string.IsNullOrEmpty(searchByUploadDate) && DateTime.TryParse(searchByUploadDate, out uploadDate);

IQueryable<YourEntity> result = db.TestUploadData2;
if (haveUploadDate)
{
    result = result.Where(a => a.UploadDate == uploadDate);
}
if (!string.IsNullOrEmpty(searchByUserName))
{
    result = result.Where(a => a.UerName.StartsWith(searchByUserName));
}
if (!string.IsNullOrEmpty(searchByReaderName))
{
    result = result.Where(a => a.ReaderName.StartsWith(searchByReaderName));
}
if (searchByReaderType != null)
{
    result = result.Where(a => a.ReaderTypeId == searchByReaderType.Value);
}

return View(result.ToPagedList(page ?? 1, 100));

这篇关于LINQ where子句为可空的datetime字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！