Andreas Lehmann : .NET 3.5

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’.

Dynamic where with Entity Framework

alehmann — Wed, 18 Mar 2009 17:18:54 GMT

In my last project I have to implement a dynamic where filter for some date stored in the Enitity Framework (EF) model. The next picture shows the DB schema of the data.

This is a common scenario to store multiple properties of a single item in a very dynamic way. The TItemProp table has a foreign key relation to the TItem table. The PropKey column in the TItemProp table holds the key of the Property (e.g. “Name”) and the PropValue column stores the value of that property (e.g. “Stephe”).

Now I have to filter some items from the TItem table with some specific properties using the EF in a simple one-to-one mapping (for more I will wait for v2 ;-).

Using LINQ to Entity a static way could be:

        1: using (EFTestEntities context = new EFTestEntities())

       2: {

       3:     var items = from item in context.TItem.Include("TItemProp")

       4:                 where item.TItemProp.Count(ip => ip.PropKey == "PropA" && ip.PropValue == "abc") > 0 &&

       5:                       item.TItemProp.Count(ip => ip.PropKey == "PropB" && ip.PropValue == "cde") > 0

       6:                 select item;

       7:  

       8:     return items.ToList();

       9: }

That is what I want to do. But I need it a bit more dynamic…

       1: using (EFTestEntities context = new EFTestEntities())

       2: {

       3:     var items = from item in context.TItem.Include("TItemProp")

       4:                 select item;

       5:  

       6:     items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == "PropA" && ip.PropValue == "abc")) > 0);

       7:     items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == "PropB" && ip.PropValue == "cde")) > 0);

       8:  

       9:     return items.ToList();

      10: }

That gives me what I want to do, but still the list of parameters is not dynamic. Therefore I will create a Dictionary which holds the Key Value collection and use a foreach loop to build the filter.

       1: Dictionary<string, string> props = new Dictionary<string, string>();

       2: props.Add("PropA", "abc");

       3: props.Add("PropB", "cde");

       4:  

       5: using (EFTestEntities context = new EFTestEntities())

       6: {

       7:     var items = from item in context.TItem.Include("TItemProp")

       8:                 select item;

       9:  

      10:     foreach (var prop in props)

      11:     {

      12:         items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == prop.Key && ip.PropValue == prop.Value)) > 0);

      13:     }

      14:  

      15:     return items.ToList();

      16: }

I thought I was done right now, but when I run my project I recognized, that my result has changed for my last approach with the foreach loop. What went wrong here, it took me some time with the SQL-Profile to figure out happen here. The generated SQL statements of all three codes where identical but in the last one the parameters went wrong. Instead of passing P1 = “PropA”, P2 = “abc”, P3 = “Propb” and P4 = “cde” the last version passes P1 = “PropB”, P2 = “cde”, P3 = “Propb” and P4 = “cde”. That where two times the last parameter. If I change the order of the parameters in my Dictionary the other parameter was passed twice. To solve this proplem I have to introduce two more variables; key and value. Assigning the values first to the variables and using them in the LINQ statement results in the correct behavior. Here is the code that worked for me:

       1: Dictionary<string, string> props = new Dictionary<string, string>();

       2:  

       3: props.Add("PropA", "abc");

       4: props.Add("PropB", "cde");

       5:  

       6: using (EFTestEntities context = new EFTestEntities())

       7: {

       8:     var items = from item in context.TItem.Include("TItemProp")

       9:                 select item;

      10:  

      11:     foreach (var prop in props)

      12:     {

      13:         string key = prop.Key.ToString();

      14:         string value = prop.Value.ToString();

      15:  

      16:         items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == key && ip.PropValue == value)) > 0);

      17:     }

      18:  

      19:     return items.ToList();

      20: }

There seems to be some strange behavior LINQ to EF is handling passed parameters. I think this is related to the deferred execution of LINQ statements and that the reference to the specific key value pair get lost.

So be careful by passing values to a LINQ statement!

RTM version of SQL Server 2008 is now available for MSDN subscribers

alehmann — Thu, 07 Aug 2008 06:43:00 GMT

The RTM (Release to Manufacturing) version of SQL Server 2008 is now available for MSDN subscribers. The following versions are ready for download:

· SQL Server 2008 Developer (x86, x64, ia64) - DVD (English)

· SQL Server 2008 Enterprise (x86, x64, ia64) - DVD (English)

· SQL Server 2008 Standard (x86, x64) - DVD (English)

· SQL Server 2008 Web (x86, x64) - DVD (English)

· SQL Server 2008 Workgroup (x86, x64) - DVD (English)

If you are an MSDN subscriber you can download it here:
http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx?PV=42:334

By the way, Microsoft although released the SQL Server Compact 3.5 SP1 as described in this blog post: http://msdnrss.thecoderblogs.com/2008/08/06/sql-server-compact-35-sp1-released/