0

I want to filter dates range. I'm using the dynamic LINQ library. I have to build my where string into a variable based on several factors and then pass the string variable to the where clause.

In my ASP.NET MVC project I wrote a helper.

private string GetFilterCondition(string filterCondition, string filterDataField, string filterValue)
{
        switch (filterCondition)
        {

            case "CONTAINS":
                return " " + filterDataField + ".Contains(\"" + filterValue + "\")";
            case "GREATER_THAN_OR_EQUAL":
                return " " + filterDataField + " >= " +"\"" + filterValue + "\"";
            case "LESS_THAN_OR_EQUAL":
                return " " + filterDataField + " <= " + "\"" + filterValue + "\"";             
        }
        return "";
}

And I used parameter in query which I've passed from the ASP.NET MVC project;

public int GetFilteredCount(string searchValue)
{
        var filteredRecords = 0;

        try               
        {                   
             var query = DB.Categories.AsQueryable();
             var totalRecords = query.Count();

             if (!string.IsNullOrEmpty(searchValue))
             {                    
                 searchValue = searchValue.Trim();        
                 query = query.Where(searchValue);
             }

             filteredRecords = query.Count();                

         }
         catch (Exception e){}
         finally
         {
             DestroyDbContext();
         }

         return result;
}

When filtering date range ("GREATER_THAN_OR_EQUAL", "LESS_THAN_OR_EQUAL"), I get an error:

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

Please note, that with 'contains' works.

I uploaded picture to see what looks like searchvalue parameter

enter image description here

I rewrote GetFilterCondition function like this:

case "GREATER_THAN_OR_EQUAL":
   return string.Format(" {0} >= DateTime.Parse(\"{1}\")", filterDataField, filterValue);

case "LESS_THAN_OR_EQUAL":
   return string.Format(" {0} <= DateTime.Parse(\"{1}\")", filterDataField, filterValue);

after executed filteredRecords = query.Count() throws 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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
www1986
  • 101
  • 1
  • 9
  • See this? http://stackoverflow.com/questions/2818044/linq-to-sql-dynamic-query-with-date-type-fields – Steve Greene Sep 04 '16 at 16:19
  • It dose not work in my case – www1986 Sep 04 '16 at 16:21
  • So you tried the solution in the link using a parameterized query? Your code as written above isn't going to work because LINQ to entities won't understand it. query.Where(searchValue) needs to have a second argument of parameters. – Steve Greene Sep 04 '16 at 16:35
  • in case with 'contains' it works and gets result, I think there is a problem with date type, but how to solve I don't know – www1986 Sep 04 '16 at 16:42
  • Dose not anyone has this kind of problem? – www1986 Sep 04 '16 at 20:40
  • Did you use a library to be able to use `query.Where(string)`? – bubi Sep 05 '16 at 05:33
  • @bubi Yes, I use it – www1986 Sep 05 '16 at 05:56
  • If you don't use the library you can query datetime type with >=. You can check where is the problem with the library or you can avoid to use the library making `GetFilterCondition` working on IQueryable (I prefere this) About library issue I think that the library is adding a where clause on a DateTime, operator >=, literal string (and it should be a DateTime). – bubi Sep 05 '16 at 06:01
  • @bubi I tried to parse to datetime (Datetime.Parse(filterValue)), but same error :( – www1986 Sep 05 '16 at 06:21
  • I changed GetFilterCondition function lke this: `return string.Format(" {0} >= DateTime.Parse(\"{1}\")", filterDataField, filterValue);` and now throws 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."** – www1986 Sep 05 '16 at 08:52
  • did not anyone have such kind of problem? – www1986 Sep 05 '16 at 16:17
  • Parse the date outside the query and set a variable. Then use it in the query. – bubi Sep 05 '16 at 17:16
  • I am parsing it outside the query – www1986 Sep 05 '16 at 17:39

0 Answers0