none
Call custom function inside projection RRS feed

  • Question

  • Hi everyone,

    Im trying to call my custom datetime parse method inside linq to entities projection like this:

    _customers
    .Select(r => new CustomerDto()
    {
       ID = r.ID,
       Created = DateTimeUtils.Parse(r.PS_TIME)
    }

    But I get this error:

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

    I googled and I found that perhaps the solution is to write some linq expression but my PS_TIME is string data type no Expression.

    Could you please help me how to parse string to datetime inside linq projection?

    Thank you for your replies.

    Wednesday, January 16, 2013 9:10 PM

Answers

  • Hi Jirí;

    I was hoping that EF had implemented an SQLFuntions class to do what you needed to do but no luck.

    One way that you can do it is as follows:

    // Change the select method as follows
    _customers
    .Select(r => new CustomerDto()
    {
       ID = r.ID,
       CreatedDB = r.PS_TIME
    }
    
    // Modify your CustomerDto class as follows
    public class CustomerDto
    {
        public int ID { get; set; }
        
        private DateTime _created
        // use this accessor to get _created as a DateTime
        public DateTime Created
        {
            get { return _created; }
            set { _created = value; }
        }
        // Use this accessor to set the value from the Database
        public string CreatedDB
        {
            set { _created = DateTimeUtils.Parse(value); }
        }
    
    } 

    Because materialization is done on the local machine your local function should be available to convert the string to DateTime.

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Jiří Straka Thursday, January 17, 2013 8:52 AM
    Thursday, January 17, 2013 5:23 AM

All replies

  • Hi Jiří;

    The problem is that the DateTimeUtils.Parse function is located in your program and when you attempt to execute a Linq to EF query EF provider can not convert that part of the query to standard SQL statements to send to the server. The EF queries are executed on the server and it has no way to access your function on your local machine.

    What does DateTimeUtils.Parse do and what does it return? Maybe there is some other way to handle this.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, January 16, 2013 9:51 PM
  • PS_TIME is string value of datetime in following format YYYYMMDDHH:MM:SS and I would like to convert it into DateTime inside the projection.

    Thank you for your ideas.

    Wednesday, January 16, 2013 10:24 PM
  • Hi Jirí;

    I was hoping that EF had implemented an SQLFuntions class to do what you needed to do but no luck.

    One way that you can do it is as follows:

    // Change the select method as follows
    _customers
    .Select(r => new CustomerDto()
    {
       ID = r.ID,
       CreatedDB = r.PS_TIME
    }
    
    // Modify your CustomerDto class as follows
    public class CustomerDto
    {
        public int ID { get; set; }
        
        private DateTime _created
        // use this accessor to get _created as a DateTime
        public DateTime Created
        {
            get { return _created; }
            set { _created = value; }
        }
        // Use this accessor to set the value from the Database
        public string CreatedDB
        {
            set { _created = DateTimeUtils.Parse(value); }
        }
    
    } 

    Because materialization is done on the local machine your local function should be available to convert the string to DateTime.

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Jiří Straka Thursday, January 17, 2013 8:52 AM
    Thursday, January 17, 2013 5:23 AM