How to use Linq for multi-column

Question

if (i == 1) //if dropdown #1 index > 0
{
    strSelectedType = ViewState["EN"].ToString();
    strSelectedCol = "Name";
}
if (i == 2) //if dropdown #2 index > 0
{
    strSelectedType = ViewState["SU"].ToString();
    strSelectedCol = "Super";
}
if (i == 3) //if dropdown #3 index > 0
{
    strSelectedType = ViewState["DT"].ToString();
    strSelectedCol = "Deran";
}
if (i == 4) //if dropdown #4 index > 0
{
    strSelectedType = ViewState["PR"].ToString();
    strSelectedCol = "PRate";
}
DataTable dtTest = new DataTable();
dtTest = (DataTable)ViewState["gvDataTable"];

DataTable selectedTable = dtTest.AsEnumerable()
              .Where(r => r.Field<string>(strSelectedCol) == strSelectedType)
              .CopyToDataTable();

The above code is only using one column to filter.

How can I modify the dtText.AsEnumerable() so that it will filter like this:

DataTable selectedTable = dtTest.AsEnumerable()
          .Where(if (dropdown #1 > 0) {r => r.Field<string>(strSelectedCol) == strSelectedType }
                if (dropdown #2 > 0) { r => r.Field<string>(strSelectedCol) == strSelectedType }
                if (dropdown #3 > 0) { r => r.Field<string>(strSelectedCol) == strSelectedType }
                if (dropdown #4 > 0) { r => r.Field<string>(strSelectedCol) == strSelectedType })
          .CopyToDataTable();

It will iterate through each dropdown append filter if the selected index > 0.

In your code i cannot be equal to two different values at same time. What is condition for filtering by several columns? — Sergey Berezovskiy
– Sergey Berezovskiy, Commented Feb 19, 2016 at 14:03
The first code section is when I was selecting one column to filter. I would like to change it so it can use one or more columns to filter. — Si8
– Si8, Commented Feb 19, 2016 at 14:05

Ian · Accepted Answer · 2016-02-19 14:21:30Z

4

There are some simplications can be made for your case, for instance prepare this first:

List<string> a = new List<string>() {"EN", "SU", "DT", "PR"};
List<string> b = new List<string>() {"Name", "Super", "Deran", "PRate"};

Then you could simply do:

strSelectedType = ViewState[a[i-1]].ToString();
strSelectedCol = b[i-1];

Then if you have list of dropdown, you could also do

var query = dtTest.AsEnumerable();

for (int i = 0; i < 4; ++i)
  if (dropdown #i-1 index > 0) {
    query = query.Where(r => r.Field<string>(strSelectedCol) == ViewState[strSelectedType].ToString());
    var result = query.CopyToDataTable();
    //Do something, your DataTable is here
  }

You could use List<KeyValuePair> too:

List<KeyValuePair<string,string>> lkvp = new List<KeyValuePair<string,string>>(){
    new KeyValuePair("EN", "Name"),
    new KeyValuePair("SU", "Super"),
    new KeyValuePair("DT", "Deran"),
    new KeyValuePair("PR", "PRate")
}

And use it like this

strSelectedType = ViewState[lkvp[i-1].Key].ToString();
strSelectedCol = lkvp[i-1].Value;

edited Feb 19, 2016 at 14:21

answered Feb 19, 2016 at 14:09

Ian

30.9k20 gold badges76 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

progpow Over a year ago

Thumb up, but maybe change two list declaring to one DIctionary?

Ian Over a year ago

@progpow ah yes! that is certainly an alternative since we only need to you key and value

progpow Over a year ago

sorry to Tuple :) We also use third type for Index Tuple<int,string,string>

Ian Over a year ago

@progpow the index is still needed. List of KVP or Tuple may also be an option.

Ian Over a year ago

@SiKni8 sorry, it seems like the fault could be mine for not understanding your question. At first, because I see your if (i == 1) if(i == 2) etc, I thought that you may only have one i value at a time, but if you need the strSelectedType/strSelectedCol to be dynamically changed, then you could put them in the for loop too. However, I cannot infer much from the question what is the exact case. A more info would be helpful

|

Ziregbe Otee · Accepted Answer · 2016-02-19 14:15:56Z

3

Use a predicate builder.

It helps especially when you don't know the exact options to filter. You simple build your predicate before you get to the point of filtering.

it improves performs of you application because you won't need to filter your collection more than once.

Read the link below: http://www.albahari.com/nutshell/predicatebuilder.aspx

edited Feb 19, 2016 at 14:15

answered Feb 19, 2016 at 14:11

Ziregbe Otee

5543 silver badges9 bronze badges

6 Comments

Sergey Berezovskiy Over a year ago

PredicateBuilder for simple filtering? Overkill

code4life Over a year ago

One word says it all: overkill.

Ziregbe Otee Over a year ago

Its best you explain how it is overkill to let us know.

Sergey Berezovskiy Over a year ago

@ZiregbeOtee add some real code and you'll see. Currently it's link-only answer

Si8 Over a year ago

@SergeyBerezovskiy Question for you, since you are using only = how will it use for more than one dropdownlist? You aren't using +=

|

Sergey Berezovskiy · Accepted Answer · 2016-02-19 15:13:22Z

2

You can build up query step by step. Just conditionally add new filters and assign result to original query variable:

var query = dtTest.AsEnumerable();

if (dropdown1.SelectedIndex > 0)
    query = query.Where(r => r.Field<string>("Name") == ViewState["EN"].ToString());

if (dropdown2.SelectedIndex > 0)
    query = query.Where(r => r.Field<string>("Super") == ViewState["SU"].ToString());

// etc

DataTable selectedTable = query.CopyToDataTable();

If you can change ViewState keys so that they'll match dropdowns ids (or vise versa), then you can easily build a list of filters and then apply them all in a loop:

var columns = new Dictionary<string, string> {
    ["EN"] = "Name",
    ["SU"] = "Super",
    ["DT"] = "Deran",
    ["PR"] = "PRate"
};

var filters = from ddl in Controls.OfType<DropDownList>()
              where ddl.SelectedIndex > 0 && columns.ContainsKey(ddl.ID)
              select new {
                  Column = columns[ddl.ID], 
                  Value = ViewState[ddl.ID].ToString()
              };

var query = dtTest.AsEnumerable();
foreach(var fitler in filters)
   query = query.Where(r => r.Field<string>(fitler.Column) == fitler.Value);

DataTable selectedTable = query.CopyToDataTable();

edited Feb 19, 2016 at 15:13

answered Feb 19, 2016 at 14:05

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

9 Comments

Si8 Over a year ago

That is what I was looking for. Thank you.

Si8 Over a year ago

Since we are only using =, what if more than one dropdownlist has a index of > 0?

Sergey Berezovskiy Over a year ago

@SiKni8 it's not clear how you are using dropdownlists. In your question there is some pseudo-code for checking this conditions. Also as you can see I use different dropdowns to build query

Si8 Over a year ago

The first code section in my question was only for one column filtering. In your code for example, you are using query = and then query = . Doesn't the second query= overwrite the first one?

Sergey Berezovskiy Over a year ago

@SiKni8 second query overwrites what is stored in query. But you are missing that second query is modified first query: query = Something(query). Thus you are keep modifying original query and storing last modifications in original variable

|

Heinzi · Accepted Answer · 2016-02-19 14:05:20Z

1

Where calls can be chained:

var rows = dtTest.AsEnumerable();

if (...)
    rows = rows.Where(r => ...);

if (...)
    rows = rows.Where(r => ...);

...

var result = rows.CopyToDataTable();

Alternatively, if you want to store the conditions for later use, you can do that as well:

var conditions = new List<Func<DataRow, bool>>();

if (...)
    conditions.Add(r => ...);

if (...)
    conditions.Add(r => ...);

...

var query = dtTest.AsEnumerable();
foreach (var cond in conditions)
    query = query.Where(cond);

var result = query.CopyToDataTable();

answered Feb 19, 2016 at 14:05

Heinzi

173k61 gold badges386 silver badges554 bronze badges

2 Comments

Si8 Over a year ago

Although it is using row = ... if more than one ddl i selected it will use for all?

Heinzi Over a year ago

@SiKni8: I am sorry, I don't understand your question.

Collectives™ on Stack Overflow

How to use Linq for multi-column

4 Answers 4

14 Comments

6 Comments

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

14 Comments

6 Comments

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest