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!
Posted
Mar 18 2009, 06:18 PM
by
alehmann