Click here to Skip to main content
15,867,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

C#
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 "
C#
LINQ to Entities does not recognize the method 'System.String ToShortDateString()' method, and this method cannot be translated into a store expression.

"

Replacing
C#
(String.IsNullOrEmpty(searchByUploadDate) || a.UploadDate.ToShortDateString() == searchByUploadDate )

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


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




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...
Posted
Updated 6-Sep-16 3:00am
v2

1 solution

Convert the date value in C#, before passing it to the query:
C#
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));
 
Share this answer
 
Comments
Umesh AP 6-Sep-16 9:28am    
@Richard - Thank you so much for reply.
Above solution works only if UploadDate (which is DateTime column) has Time equals to 00:00:00.000.
How to modify above to works only on date part ignoring time part.
Richard Deeming 6-Sep-16 9:33am    
Try either:
result = result.Where(a => DbFunctions.TruncateTime(a.UploadDate) == uploadDate);

or:

DateTime uploadDateMax = uploadDate.AddDays(1);
result = result.Where(a => a.UploadDate >= uploadDate && a.UploadDate < uploadDateMax);



The second option is probably better, as SQL will be able to use an index on the column to satisfy the query.
Umesh AP 6-Sep-16 9:50am    
@Richard Deeming - Thank you so much for valuable support....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900