none
how to filter Query by date in Dynamic LINQ RRS feed

  • Question

  • hi

    i am using EF withe Oracle DB

    and i want to filter my Table according to the user input (13 optional input)

    i used LINQ Dynamic Query Library to build my query

    but when i filter using date input  for example :20-2-2012

    it give 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.

    this is my Query

     public void CreatSrtWhere()
            {
                string strWhere = string.Empty;
    
                if (!string.IsNullOrWhiteSpace(TBOX_MODULE.Text))
                { //if 0
                    if (!string.IsNullOrEmpty(strWhere))
                   {
                       strWhere = "AND";
                       strWhere = "   DEV_MODEL_NAME==\"" +TBOX_MODULE.Text +"\"";
                   }
                    
                   else
                   {
                       strWhere = "DEV_MODEL_NAME==\"" + TBOX_MODULE.Text + "\"";
                   }
    
    
                } //if 0
    
    if (!string.IsNullOrWhiteSpace(TBOX_DEV_TYPE.Text))
                    { //if8
                        if (!string.IsNullOrEmpty(strWhere))
                        {
                            strWhere = strWhere + "  And  DEV_TYPE_CODE= " + TBOX_DEV_TYPE.Text;
    
                        }
                        else
                        {
                            strWhere = "   DEV_TYPE_CODE= " + TBOX_DEV_TYPE.Text;
                        }
    
                    } //if8
    
       if (!string.IsNullOrWhiteSpace(TBOX_STATUS_DATE.Text))
                    { //if10
                        if (!string.IsNullOrEmpty(strWhere))
                        {
                            strWhere = "  and  DEV_STATUS_DATE== DateTime.Parse(\"" + Convert.ToDateTime(TBOX_STATUS_DATE.Text) + "\")";
    
                        }
                        else
                        {
    
                            strWhere = "  DEV_STATUS_DATE== DateTime.Parse(\"" + Convert.ToDateTime(TBOX_STATUS_DATE.Text) + "\")";
                        }
    
                    } //if10

    i try to chang the code to

    strWhere = "  DEV_STATUS_DATE== " + Convert.ToDateTime(TBOX_STATUS_DATE.Text)  ;

    but it give this error

    Operator '==' incompatible with operand types 'DateTime?' and 'Int32'

    how to fix this ??



    Tuesday, March 25, 2014 8:17 AM

Answers

  • As a workaround, please consider pass the DateTime value as parameter instead:

    var x = DateTime.Parse("04/30/2012"); 
    
                using (DFDBEntities db = new DFDBEntities())
                {
                    var result = db.TableWithDateTimes.Where("DateTimeColumn = @0", x).ToList();
                }
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 26, 2014 8:25 AM
    Moderator

All replies

  • Hello,

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

    This error is caused because when the LINQ query is translated to TSQL, the CLR method'System.DateTime Parse(System.String)' cannot be translated to be TSQL method and there are only a few methods can be translated to TSQL.

    There are two ways to fix this issue. The first is that we can load data to memory using .ToList()/.IEnumable() method. Then we can use these CLR methods as 'System.DateTime Parse(System.String)' method in the LINQ query. But this way may have the performance issue since it will load all table data to memory firstly.

    Another way is to replace the results of the calls to DateTime.Parse() and use those variables in the query it should work fine as:

    var from = DateTime.Parse("10/01/2011");
    
    var to = DateTime.Parse("04/30/2012");
    
    var query = from c in context.tblClients
    
                        where (c.FirstName != null || c.LastName != null)
    
                           && c.EligibilityDate >= from
    
                           && c.EligibilityDate <= to
    
                          orderby c.ClientID
    
                        select c;
    
    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 26, 2014 2:10 AM
    Moderator
  • thanks

    that why i use Dynamic Query Library insted of IEnumable() method

    also i try to use the dateTime.pars

    var x = DateTime.Parse("04/30/2012");
                            strWhere = "  DEV_STATUS_DATE =="+ x ;

    but is give this error

    Operator '==' incompatible with operand types 'DateTime?' and 'Int32'

    it seems that it read the value of x as intger

    and if i change the where cluse to :

      strWhere = "  DEV_STATUS_DATE ==\""+ x + "\"" ;

    it will read the value of x as string and show this error

    Operator '==' incompatible with operand types 'DateTime?' and 'String

    i dont know how to fix this ?

    Wednesday, March 26, 2014 5:57 AM
  • As a workaround, please consider pass the DateTime value as parameter instead:

    var x = DateTime.Parse("04/30/2012"); 
    
                using (DFDBEntities db = new DFDBEntities())
                {
                    var result = db.TableWithDateTimes.Where("DateTimeColumn = @0", x).ToList();
                }
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 26, 2014 8:25 AM
    Moderator
  • i am creating dynamic linq query  as following

    whereClause = string.Format(@"DateDeposit{0}{1} {2} DateDeposit{3}{4}", operator1, dtFrom, logic, operator2, dtTo);

    operator1 and operator2 are comparison operator,

    dtFrom iand dtTo s a date,

    logic is a conditional operator.

    but i am getting error like operator ' =' incompatible with operand types 'datetime' and 'int32'.

    How to resolve this issue.

    Monday, December 9, 2019 10:00 AM