Dynamic Filtering Using Linq Query Self-Wrote Library

What you will find in this post:

1. The source code for the library
2. How to use the library
3. Main concept implemented through the library
4. Comparison operators currently supported by the library

What you will NOT find in this post:

1. Explanation of the source code of the library

Limitations on the library:

1. Needs system user input module to accept conditions in a certain form (discussed below in the main concept implemented)
2. Works only with data collections which implement the "IQueryable" interface

To jump to the point, imagine that you are working on a windows application or an ASP.Net application and you have a data set which you want your system user to filter dynamically. In other words, you want the system user to choose the columns he wish to filter by at run-time.

This means that at the moment of writing the code, you don't know the exact condition to filter your data set by. Some will think to implement every single probability so that they have to implement the functionality of filtering by first column, another one for filtering by second column and finally another one for filtering by both columns.

Ok, although I don't recommend, but, this may work for simple entities with so few columns (properties) but not for complex ones. Imagine if your data set has 10 columns and you wish all these 10 columns are valid to be used for filtering. To implement the same solution above you will have so much pain and for sure the probability of having mistakes and issues is so great. this is beside your code being so hard to be maintained and extended someday ... imagine a new requirement to add 2 more columns :)

So, now you have to think in a more innovative way to make your code generic, clean, easy to manage and extensible. That is what I tried to do here and I think I achieved something good :)


Main concept implemented:

• All conditions (and/or) can be transformed to a flat form however complex these conditions are
• Assume we have conditions A, B, C, D, E & F
• Also, assume that the anding is represented by "&" and the oring by "|"
• Then (A & B) ==> (A & B)
• (A | B) ==> (A) | (B)
• (A | (B & C)) ==> (A) | (B & C)
• (A & (B | C)) ==> (A & B) | (A & C)
• (A & (B | C)) & (D & (E | F))
• ==> [(A & B) | (A & C)] & [(D & E) | (D & F)]
• ==> [(A & B) & (D & E)] | [(A & B) & (D & F)] | [(A & C) & (D & E)] | [(A & C) & (D & F)]
• ==> (A & B & D & E) | (A & B & D & F) | (A & C & D & E) | (A & C & D & F)

So, from the examples above, you will understand that any condition however complex it is can be transformed into a flat form which is (groups, each group consists of anded conditions, these groups are ored together). This concept is pure logic and mathematically derived.

This concept is already used in SQL management studio. If you write a view with some complex conditions, then you right-click on the view and choose to script it as create, you will find that the view in the generated script is implemented in a different way cause management studio has flattened the conditions of the view to be optimized for performance.

Anyway, this is the main concept used in this library and this will require the input of the library to be in a similar form.


How the library is implemented:

1. The library is implemented in the form of extension methods for the "IQueryable" interface. These extension methods will provide:
1. An extra overload for the "Where" method which takes an "OringGroup" instance as input
2. The new "WhereNot" method which takes an "OringGroup" instance as input
2. The library provides the definition of the "OringGroup" class which is a collection of the "AndingGroup" class
3. The library provides the definition of the "AndingGroup" class which is a collection of the "AndingItem" class
4. The library provides the definition of the "AndingItem" class which holds the main aspects of a condition
5. All items (AndingItem) in the "AndingGroup" class instance will be anded with each other
6. All items (AndingGroup) in the "OringGroup" class instance will be ored with each other

Comparison operators currently supported by the library:

1. Equal
2. NotEqual
3. GreaterThan
4. GreaterThanOrEqual
5. LessThan
6. LessThanOrEqual
7. RegexMatch
8. Contains

Can we add more comparison operators to the library?
Yes


How to use the library:

1. Add reference to the library project/DLL
2. Add the "using DynamicLinqExtensionMethodsNS;" to the using section
3. Follow the example below:
4. This is a dummy entity to use in a collection
   

    public class Order  
      {  
        string text;  
        public string Text  
        {  
          get { return text; }  
          set { text = value; }  
        }  
      
        int index;  
        public int Index  
        {  
          get { return index; }  
          set { index = value; }  
        }  
      
        public Order()  
        {  
      
        }  
      
        public Order(string _text, int _index)  
        {  
          Text = _text;  
          Index = _index;  
        }  
      }  
   

5. Creating a list of this dummy entity to use as the main data set to be filtered at run-time
   

    List<Order> lst = new List<Order>();  
                lst.Add(new Order("a",1));  
                lst.Add(new Order("b",4));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("c",7));  
                lst.Add(new Order("d",9));  
                lst.Add(new Order("a",14));  
                lst.Add(new Order("a",2));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("ahmed tarek hasan", 0));  
                lst.Add(new Order("mohamed emad eldin", -1));
   

6. Creating "OringGroup" instance and populating it with the conditions to be used for filtering
   

    OringGroup org = new OringGroup();  
      
    AndingGroup ang1 = new AndingGroup();  
    ang1.Add(new AndingItem(){ PropertyName = "Text", Value = "a", ComparisonOperator = OperatorType.Equal});  
    ang1.Add(new AndingItem(){ PropertyName = "Index", Value = 5, ComparisonOperator = OperatorType.Equal});  
      
    AndingGroup ang2 = new AndingGroup();  
    ang2.Add(new AndingItem() { PropertyName = "Text", Value = "b", ComparisonOperator = OperatorType.Equal });  
    ang2.Add(new AndingItem() { PropertyName = "Index", Value = 4, ComparisonOperator = OperatorType.Equal });  
      
    AndingGroup ang3 = new AndingGroup();  
    ang3.Add(new AndingItem() { PropertyName = "Index", Value = 7, ComparisonOperator = OperatorType.GreaterThanOrEqual });  
      
    AndingGroup ang4 = new AndingGroup();  
    ang4.Add(new AndingItem() { PropertyName = "Text", Value = "tarek", ComparisonOperator = OperatorType.RegexMatch });  
      
    AndingGroup ang5 = new AndingGroup();  
    ang5.Add(new AndingItem() { PropertyName = "Text", Value = "emad", ComparisonOperator = OperatorType.Contains });  
      
    org.Add(ang1);  
    org.Add(ang2);  
    org.Add(ang3);  
    org.Add(ang4);  
    org.Add(ang5);  
   

7. Using the library in filtering
   

    var t = lst.AsQueryable().Where(org).ToList();  
    //var t = lst.AsQueryable().WhereNot(org).ToList();  
    dataGridView1.DataSource = t;  
   

8. Watch the results and say "Yesssssssssssssssss" :)

How will this work in real system:

1. Build your system UI to collect conditions (columns to filter by, comparison operators & comparison values)
2. Create the "AndingItems" by setting:
1. PropertyName ==> the name of the "property" in the entity you are filtering on
2. Value ==> the value to be compared to
3. ComparisonOperator ==> the comparison operator to be used for the filtering
3. Create an "AndingGroup" for each set of "AndingItems" which will be anded together at run-time condition evaluation
4. Create an "OringGroup" and add to it all "AndingGroup" instances created in the previous step
5. Now, if your collection implements the "IQueryable" interface, you will find the extra overload of the "Where" which takes "OringGroup" as input. Also, you will find the new "WhereNot" method which also takes "OringGroup" as input
6. When the "Where" or "WhereNot" method is executed, the library constructs the full Linq condition then the filtering is triggered at once, not at round trips for each condition

Can I redistribute this library:
Sure you can but please try to mention the main source (this blog).


Where is the download link for the library source code:
You can download the library source code from here



Finally, I hope you find this library helpful and for sure all your comments are welcomed :)