Solved

Linq error filtering date

Posted on 2013-12-02
12
375 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
  • 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 74

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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 74

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 74

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
The viewer will learn how to count occurrences of each item in an array.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now