Solved

Linq error filtering date

Posted on 2013-12-02
12
404 Views
Last Modified: 2016-02-10
Hi experts,

I'm trying to get into asp.net MVC with linq, it's a bit of a learning curve for me coming from an Microsoft Access background. I'm pressing in and working through a few tutorials, one of which is the Contoso University Tutorial which is great. I'm messing about with the code to gain a deeper understanding of how things work, one of the things I tried to do was to filter by a date and I got the attached screen dump error using the code below.
       public ActionResult Index(string sortOrder="Name", string searchString="")
        {
            ViewBag.NameSortParm = sortOrder == "Name" ? "Name_desc" : "Name"; //String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "Name";
            ViewBag.FNameSortParm = sortOrder == "FName" ? "FName_desc" : "FName";//String.IsNullOrEmpty(sortOrder) ? "FName_desc" : "FName";
            ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
            var students = from s in db.Students
                           select s;

            if (!String.IsNullOrEmpty(searchString))
            {
                students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())
                                       || s.FirstMidName.ToUpper().Contains(searchString.ToUpper())
                                       || s.EnrollmentDate.Date.ToString().Contains(searchString.ToUpper()));
            }

            switch (sortOrder)
            {
                case "FName_desc":
                    students = students.OrderByDescending(s => s.FirstMidName);
                    break;
                case "FName":
                    students = students.OrderBy(s => s.FirstMidName);
                    break;
                case "Name_desc":
                    students = students.OrderByDescending(s => s.LastName);
                    break;
                case "Name":
                    students = students.OrderBy(s => s.LastName);
                    break;
                case "Date":
                    students = students.OrderBy(s => s.EnrollmentDate);
                    break;
                case "Date_desc":
                    students = students.OrderByDescending(s => s.EnrollmentDate);
                    break;
                default:
                    students = students.OrderBy(s => s.LastName);
                    break;
            }
            return View(students.ToList());
        }

Open in new window

The error is... LINQ to Entities does not recognise the method 'System.String ToString()' Can anyone show me how to correct my filter so it will work with full dates and partial dates eg. 10/01/2010 or 10/01 or even 01/2010.

Thanks in advance,

Joe
Capture.JPG
0
Comment
Question by:dataflowjoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
12 Comments
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 250 total points
ID: 39690908
I think you are getting error in s.EnrollmentDate.Date.ToString()

so first apply a null check for s.EnrollmentDate != null && s.EnrollmentDate.ToString().Contains(...
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39690941
What check are you trying to do? You are converting a date to a string, and then checking if that string contains some substring. This is generally not advisable when working with dates. I would expect you to compare two dates. Is that not feasible here?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 39690952
Thanks for quick reply, I've implemented your suggestion and I'm still getting this error...

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

Any more suggestions?
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39690977
<link to non-authoritative, and defective competing site deleted by COBOLdinosaur, Topic Advisor>

ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 39690985
To kaufmed,

Surely I should be able to search for a substring of a date? Let's say I just want a simple filter that picks out all records for a given year. If I know the date format is in the UK format then I might want to see all records for 11/2010 that will be November 2010, I know the US puts the month and day differently, but if all my users were in the UK then I'd anticipate that.

Thanks
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39690994
If you want to complare in such a way, with Date type we have Day, Month, Year as property, why you not use that to compare each and every property to match it, make sense?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 39691036
Hi VJSoft,

I've implemented suggestion and still get error. I changed the code to the following...
string strItem = searchString.ToString();
                students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())
                                       || s.FirstMidName.ToUpper().Contains(searchString.ToUpper())
                                       || s.EnrollmentDate.ToString().Contains(strItem));

Open in new window


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

The reason that I'm using this simplistic method because there is just one search box to cover 3 fields. In microsoft access this is quite simple to do, so I guessed that it would be the case here. Another idea would be to have a hidden field that concatenates the 3 fields together and search the concatenated field for any contained value.
I'm just doing all this purely as an exercise to gain a better understanding in search methods.
I appreciate all the help!
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39691113
Surely I should be able to search for a substring of a date?
As VjSoft mentioned above, EF is a bit finicky when it comes to what you can and can't do on the .NET side. All of your LINQ gets turned into SQL (the same way it does in LINQ-to-SQL). The EF engine inspects each part of your LINQ query to find a matching SQL operator. There isn't one for ToString. This is why you get the error.

Are the dates stored in your database as strings? If so, why would you use that data type to begin with, and is there any particular format to an arbitrary date string? Can you safely convert them to actual date/time instances without error?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 39691283
Hi Kaufmed, the dates are stored as datetime datatypes, so I guess if I convert all potential search fields to varchar datatypes and cocatenate  them into a single hidden field I could use that as a simple generic search field, would that be one way of doing this? I know that I could set up a separate search on the date using a range etc, but I was just looking for a single search field that would do for all fields that are to be searchable.
Appreciate your anwers, it's helping me understand the limitations and potential workarounds!
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 39700209
Since your database has the data stored in a datetime data type, your entity should match that. You can simply use the properties of the DateTime struct to filter in your query.

e.g.

students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper()) ||
                               s.FirstMidName.ToUpper().Contains(searchString.ToUpper()) ||
                               (s.EnrollmentDate.Year == 2000 && s.EnrollmentDate.Month == 11));  // 11/2000

Open in new window

0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 39733313
Thanks guys
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question