why the date conversion is not working in linq

Question

I am new to mvc i had problem with following code it is throwing an exception: LINQ to Entities does not recognize the method 'System.String ToString(System.DateTime)' method, and this method cannot be translated into a store expression.

     public ActionResult JobSearchList(PostJobModel model)
     {
         try
         {

         if (Session["USER_ID"] != null)
         {
             var subscriber_Id = RL_Constants.RES_ID;
             var company_Id = RL_Constants.COMP_ID;
             var resource_Typa = RL_Constants.RES_TYPE;
             var jobPostDetails = (from jobPost in reslandentity.JOB_POSTING
                                   where jobPost.COMP_ID == RL_Constants.COMP_ID
                                   select new PostJobModel 
                                   {  
                                       POST_DT=Convert.ToString(jobPost.POST_DT),
                                       POST_END_DT=Convert.ToString(jobPost.POST_END_DT),
                                       POSITIONS_CNTS=Convert.ToString(jobPost.POSITIONS_CNT),
                                       JOB_TYPE = jobPost.JOB_TYPE,
                                       SKILLS = jobPost.SKILLS,
                                       DURATION = jobPost.DURATION,
                                       CATEGORY=jobPost.CATEGORY,
                                       PREREQUISITES = jobPost.PREREQUISITES,
                                       LOCATION=jobPost.LOCATION,
                                       RATE=jobPost.RATE,
                                       PERKS = jobPost.PERKS,
                                       CONTACT_PERSON=jobPost.CONTACT_NAME,
                                       CONTACT_EMAIL=jobPost.CONTACT_INFO,
                                       CONTACT_PHONE=jobPost.CONTACT_INFO,
                                       POST_TITLE=jobPost.TITLE,  
                                       DESCRIPTION=jobPost.DESCR                                                                                                                                                                    
                                   }).ToList();
             model.GetJobPostDetails = jobPostDetails;



         }

         return View(model);
     }
        catch (Exception ex)
        {

            throw ex;
        }

     }

Why is your `PostJobModel` using strings to represent date/time values in the first place? That sounds like the thing to fix (along with your indentation, which makes you question hard to read). — Jon Skeet, Jul 29 '14 at 09:19
http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities — Tim Schmelter, Jul 29 '14 at 09:20
this has nothing to do with MVC - this is an LINQ/LINQ2EF problem - and to be honest: the error-message says it all! The framework does not know how to translate your `Convert.ToString` into SQL ... — Random Dev, Jul 29 '14 at 09:25

James · Accepted Answer · 2014-07-29T10:08:29.577

1

The error is pretty self explantory, ToString is not supported by the LINQ provider therefore you can't use it in a query.

Pull down the raw data and perform your conversions in memory.

var jobPostDetails = reslandentity.JOB_POSTING
   .Where(x => x.COMP_ID == RL_Constants.COMP_ID)
   .AsEnumerable() // materialize query, Select will be performed in memory
   .Select(x => new {
       POST_DT = x.POST_DT.ToString(),
       POST_END_DT = x.POST_END_DT.ToString(),
       POSITIONS_CNTS = x.POSITIONS_CNT.ToString(),
       ...
   })
   .ToList();

AsEnumerable will switch the context from IQueryable<T> to IEnumerable<T> therefore allows for CLI-specific methods to be called as part of the query. The query will still only be materialized after ToList is called as AsEnumerable retains delayed execution.

edited Jul 29 '14 at 10:08

answered Jul 29 '14 at 09:25

James

80,725
18
167
237

This is only a good aproach if you really want to materialize this query into memory. – Johannes Wanzek Jul 29 '14 at 09:40
@JohannesWanzek the only part materialized into memory is the projection, the actual filtering is done server-side. The fact the OP needs to perform an unsupported method means they have to do this anyway. Not sure why this answer has been downvoted... – James Jul 29 '14 at 09:43
@James Maybe in this situation it works, but using LINQ it's not a good approach to do the materialization manually. Even with ToList. – Johannes Wanzek Jul 29 '14 at 09:52
@JohannesWanzek that statement is completely bogus, there *loads* of scenarios where materializing a query early is necessary, this is a primary example. In fact, `AsEnumerable` retains delayed execution and is the recommended solution for scenarios like this - you shouldn't downvote answers if you don't fully understand them. – James Jul 29 '14 at 09:59
@James Didn't know `AsEnumerable` retains delayed execution. So good answer! :) – Johannes Wanzek Jul 29 '14 at 10:06

score 1 · Answer 2 · answered Jul 29 '14 at 09:34

Take a look at SqlFunctions when trying to convert something inside a LINQ Query.

SqlFunctions.DateName("dd", jobPost.POST_DT) + 
SqlFunctions.DateName("mm", jobPost.POST_DT) + 
SqlFunctions.DateName("yyyy", jobPost.POST_DT) +

This functions can also be used inside where clauses etc.

why the date conversion is not working in linq

2 Answers2