I have various windows forms applications that contain various controls that build up custom "group by" and "where" clauses for my class methods.

I am thinking to pass a collection structure of some kind in order for the methods to be able to build the group by's and the where clauses.

Let's say a list containing the field and the value for the group by or where.

Am I going to the right direction?


I dont have code yet but let me try to explain better:

  1. We have a form with 10 dropdownlists and a checkmark beside them.
  2. Also we have adatagrid on the form.
  3. Every dropdownlist corresponds to a field in a table.

    • If a dropdownlist has its checkbox checked means that it must be part of the group by clause in the select statement.

    • The value of the dropdownlist must be part of the where clause.

*note: * Another application could have different numbers of dropdownboxes and different corresponding fields.

I want to create a common method that will retrieve data with user selectable group by and where clauses.

So what i think is to pass to the method 2 lists.

  • One with (fieldname, value) for the where clauses

  • And one with (fieldname) for the group by

depending on the logic above...

I hope this is more clear.

  • Can you post a code sample? I can't make heads nor tails from your description.
    – Oded
    Commented Apr 18, 2012 at 10:31
  • so, you want a 'control' that takes a list of TType, and allows the user to filter the displayed items by fields in a database? I guess you can't do the filtering in memory? You could make a generic repository interface which allows filtering on fields, either stringly typed, expression-evaluated or something else. :)
    – Max
    Commented Apr 18, 2012 at 11:06
  • I am not sure i follow :).. All i want is the best way to pass the group variables and where to the method that does the operation and i am asking whether i do it in a list or hashtable,array e.t.c
    – e4rthdog
    Commented Apr 18, 2012 at 11:33
  • Oh sorry, I misunderstood you. If you already have the method nailed down, the rest should follow. Do the methods take strings for fields and values? If so, you could just use a dictionary for the where-info and a list for the groupby-info.. :)
    – Max
    Commented Apr 18, 2012 at 12:24
  • Giving more thought in it, i could use dictionary for both of them and just passing the key,value pairs from my form elements, and avoid list and object creation for the list....correct?
    – e4rthdog
    Commented Apr 18, 2012 at 12:53

2 Answers 2


Have you considered using LINQ/ an expression-tree? It seems exactly to match your problem, unless I've missed something.

Whatever the detail of the particular collection-type, as long as it's enumerable, you can use all the benefits of LINQ. You have the datasource of your gridview (or whatever the datasource is); you can then attach events to the check-boxes/ drop-downs that add/remove the where/group-by based on the binding that they have to the fields in the database.

Personally, I'd also design the system without much regard to the persistence mechanism, you want an easily-understood abstraction model at each level, it is rare that the same model is appropriate in different layers.

I'm assuming you're using at least C# 3.

  • The truth is that i try to "resist" to Entity Framework and LINQ, because i am new to C# programming. I try to follow the conventional way and then advance. But it seems that LINQ wont hurt right now..Also, the source of the grid results are too many records (all the inventory lots) to have them attached to the grid. So i have to run queries against the database every time. Imagine that i will show 100-200 records every time from thousands of rows.I think also that you are correct on being more relaxed on my class library method design....
    – e4rthdog
    Commented Apr 18, 2012 at 16:11
  • @e4rthdog LINQ is not just for databases and doesn't require ORM (Object Relational Mapping). You can run LINQ queries on any collection data type. Commented Apr 18, 2012 at 21:24
  • yeap i got it..i started reading about it it its a shame i havent done until now :)
    – e4rthdog
    Commented Apr 19, 2012 at 7:35
  • 1
    @e4rthdog: Yes, mainly the difference between LINQ on IEnumerable and LINQ on IQueryable. Really, take a leap into LINQ, you'll wonder how you lived without it. As for EF, I share your concerns, especially as it's not the magic-bullet that ORMs tend to be sold as. Commented Apr 19, 2012 at 9:50

The logic for Group By is very complex. First, you should ask yourself, is this something you need/has the resources to program on your own, or is it better to use a tool for that? For example, if you group by a timestamp, you may decide to group only by date, hours and minutes and ignore the rest. You also have to use the real data values and not the formatted strings, etc.

There are 3rd party tools that help you do this nicely, easily and provide great GUI effects like drag and drop of your grid columns into grouping area (much like Outlook).

Now, if you decided to do it on your own, I suggest you perform the grouping in the database not on the client. There are many reasons for that such as:

  • This will let you not worry about memory management for large lists

  • Coding and testing will be simplified

  • You don't have to worry about data types in comparison or in filtering

  • You will get up-to-date data

Going forward with this approach, you can ask the user to select the group by fields and every selected field can be used to build a string. When the user selects all desired fields, you end up with a string like:

"Country, Gender, Age"

Do the same for the grouping condition (MS Access for example has an expression builder which I think is not necessary, but it helps end user build an expression) and eventually end up with a string such as:

"Age > 12"

Now you could plug these strings into your original select query to get:

SELECT ... GROUP BY Country, Gender, Age HAVING Age > 12

The other tricky part is to figure out what your user wants to do with the group by. Usually you want to run an aggregate function like SUM or COUNT on the grouped set. So you need to take this in consideration also.

  • thanks! I do know the group hierarchy that i want and thank god is standard and not dynamic. I thought initially to do it like this, by just building 2 strings one for where and one for the group by and i would need one for the final selected fields,expressions. I was just feeling that best practices would suggest something else more "sophisticated" :). After all what i want is to be able to distribute this class and have anyone create whatever form they like and do their one where and group criteria. I just want my methods to provide solid business logic...
    – e4rthdog
    Commented Apr 18, 2012 at 12:57
  • 1
    Thanks for your comment, in that case, you'd pass the lists as you already mentioned in your post. You need to be careful about the Group By fields order.
    – NoChance
    Commented Apr 18, 2012 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.