How to use Entity Framework to query string values with filter conditions

alehmann — Wed, 08 Apr 2009 12:57:33 GMT

Have you ever tried to filter a LINQ statement by string values. E.g. you want to get all persons where the name starts with letters from ‘A’ to ‘Z’. In this post I will show you how to build a query like this.

My first approach was to do a simple <= operation like I do in SQL. But this results in an exception: “Operator '<=' cannot be applied to operands of type 'string' and 'string'”. OK, I remember, we are in the .NET world. So I tried the .CompareTo() method of string. And it seems to work. So my LINQ statement would be:

        1: string valueMin = "A";

       2: string valueMax = "Z";

       3:  

       4: var x = from p in context.TPerson

       5:         where p.Name.CompareTo(valueMin) >= 0 &&

       6:               p.Name.CompareTo(valueMax) <= 0

       7:         select p;

Imagine a list of people: “Anton”, “Bill”, “Hans”, “Hugo”, “Steve” and “Xavier”. When I set the valueMin to ‘A’ and valueMax to ‘Z’ I receive all names. When I set valueMax to ‘X’, “Xavier” is NOT returned. That is because “Xavier”.CompareTo(“X”) returns 1 and 1 is not <= 0. Writing my own string comparer is not possible in this scenario because it would not be used by the Entity Framework. So I have to find a workaround. And here is the LINQ statement that works fine for me:

       1: string valueMin = "A";

       2: string valueMax = "Z";

       3:  

       4: var x = from p in context.TPerson

       5:         where p.Name.ToLower().Substring(0, valueMin.Length).CompareTo(valueMin.ToLower()) >= 0 &&

       6:               p.Name.ToLower().Substring(0, valueMax.Length).CompareTo(valueMax.ToLower()) <= 0

       7:         select p;

Remembering the above problem I have to find a way where “Xavier”.CompareTo(“X”) results in –1 or 0 to make my query work. So –1 would be the wrong result, what I really want is that it return 0. CompareTo() will return 0 when both values are equal. So lets make them equal… Therefore all values are converted to lower (or upper) case. Than we shorten the queried value to the length of the filter value. In result we get the following statement: “x”.CompareTo(“x”) which results in 0. So the above query works as expected.

The query by valueMin = ‘A’ and valueMax = ‘A’ returns all persons where the name starts with ‘A’.

Andreas Lehmann : LINQ

How to use Entity Framework to query string values with filter conditions