DataTable select distinct values from several columns

as allways i need your help :)

i got a DataTable which has the following columns:

TITLE ADDRESS BRANCH BRANCH1 BRANCH2 BRANCH3

since i dont want to execute another MySql query to get only the branches, i figured i can do a LI开发者_如何学JAVANQ query on the DataTable and get distinct values.

boy, i was wrong... its not as simple i thinked it will be.

anyone can help me with this ? ( Lambda expression prefered )

edit:

if i wasnt clear, i need all the branches, from all the rows.

---

Try this:-

var rows = dataTable.AsEnumerable();
rows.Select(x => x["BRANCH"])
    .Union(rows.Select(x => x["BRANCH1"]))
    .Union(rows.Select(x => x["BRANCH2"]))
    .Union(rows.Select(x => x["BRANCH3"]));

This takes all the values from all the BRANCH columns in your table and uses Union() to a) join them into a single list and b) remove duplicates, giving you distinct values.

(NOTE: The Union() function removes duplicate values. In order to preserve duplicates, Concat() can be used in place of Union())

--- EDIT ---

If you want to discard null values, user Where(), e.g.:-

var rows = dataTable.AsEnumerable();
rows.Select(x => x["BRANCH"])
    .Union(rows.Select(x => x["BRANCH1"]))
    .Union(rows.Select(x => x["BRANCH2"]))
    .Union(rows.Select(x => x["BRANCH3"]))
    .Where(x => x != null);

---

Define your own IEqualityComparer

public class MyComparer : IEqualityComparer<DataRow>
{
   public bool Equals(DataRow x, DataRow y) {
      // logic to distinguish the branches
      // this is just an example.  
      return x["BRANCH"] == y["BRANCH"] 
                && x["BRANCH1"] == y["BRANCH1"]
                && x["BRANCH2"] == y["BRANCH2"]
                && x["BRANCH3"] == y["BRANCH3"];

   }

   public int GetHashCode(DataRow obj) {
      // logic to distinguish the branches
      // this is just an example.
      return obj["BRANCH"].GetHashCode() +
                  obj["BRANCH1"].GetHashCode() +
                  obj["BRANCH2"].GetHashCode() +
                  obj["BRANCH3"].GetHashCode() ;
   }
}

and use it

var result = dataTable.AsEnumerable().Distinct(new MyComparer());