Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linq error filtering date

Posted on 2013-12-02
12
Medium Priority
?
419 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 1000 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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
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 1000 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

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

Question has a verified solution.

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

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will learn how to dynamically set the form action using jQuery.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

636 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