By Ahmed


2009-07-29 10:03:51 8 Comments

I have a dataset objds. objds contains a table named Table1. Table1 contains column named ProcessName. This ProcessName contains repeated names.So i want to select only distinct names.Is this possible.

  intUniqId[i] = (objds.Tables[0].Rows[i]["ProcessName"].ToString());

18 comments

@Gál Gyula 2018-12-14 09:37:11

objds.Table1.Select(r => r.ProcessName).AsEnumerable().Distinct();

@Erick Petrucelli 2018-12-14 13:25:06

Hi @GálGyula, welcome to Stack Overflow! Here we care about answers with good explanations, not just code. Only post an answer if it really solves the question and you can explain how. To improve your future answers, take a look the the guide how do I write a good answer.

@Manish Singh 2018-08-16 10:05:28

it is easy

    DataView view = new DataView(dt);
DataTable dt2 = view.ToTable(true, "Column1", "Column2","Column3", ...,"ColumnNth");

and dt2 datatable contain column1,Column2..ColumnNth unique data.

@Davide Castronovo 2018-01-31 13:49:58

The most simple solution is use linq and then transform the result to a DataTable

    //data is a DataTable that you want to change
    DataTable result = data.AsEnumerable().Distinct().CopyToDataTable < DataRow > ();

This is valid for asp.net 4.0 ^ Framework if I remeber.

@Packiyaraj Shanmugam 2017-06-21 07:30:08

DataTable dtbs = new DataTable(); 
DataView dvbs = new DataView(dt); 
dvbs.RowFilter = "ColumnName='Filtervalue'"; 
dtbs = dvbs.ToTable();

@gideon 2010-12-01 17:05:27

I just happened to find this: http://support.microsoft.com/default.aspx?scid=kb;en-us;326176#1

While looking for something similar, only, specifically for .net 2.0

Im assuming the OP was looking for distinct while using DataTable.Select(). (Select() doesn't support distinct)

So here is the code from the above link:

class DataTableHelper 
{
    public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
    {   
        DataTable dt = new DataTable(TableName);
        dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

        object LastValue = null; 
        foreach (DataRow dr in SourceTable.Select("", FieldName))
        {
            if (  LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) ) 
            {
                LastValue = dr[FieldName]; 
                dt.Rows.Add(new object[]{LastValue});
            }
        }

        return dt;
    }

    private bool ColumnEqual(object A, object B)
    {

        // Compares two values to see if they are equal. Also compares DBNULL.Value.
        // Note: If your DataTable contains object fields, then you must extend this
        // function to handle them in a meaningful way if you intend to group on them.

        if ( A == DBNull.Value && B == DBNull.Value ) //  both are DBNull.Value
            return true; 
        if ( A == DBNull.Value || B == DBNull.Value ) //  only one is DBNull.Value
            return false; 
        return ( A.Equals(B) );  // value type standard comparison
    }
}

@Vijay Balani 2012-07-11 01:11:16

Following works. I have it working for me with .NET 3.5 SP1

// Create the list of columns
String[] szColumns = new String[data.Columns.Count];
for (int index = 0; index < data.Columns.Count; index++)
{
    szColumns[index] = data.Columns[index].ColumnName;
}

// Get the distinct records
data = data.DefaultView.ToTable(true, szColumns);

@user3639409 2014-05-26 11:23:15

Syntax:-

DataTable dt = ds.Tables[0].DefaultView.ToTable(true, "ColumnName");

EX:-

DataTable uniqueCols = dsUDFlable.Tables[0].DefaultView.ToTable(true, "BorrowerLabelName");

@Tanmay Nehete 2015-09-14 05:39:20

string[] TobeDistinct = {"Name","City","State"};
DataTable dtDistinct = GetDistinctRecords(DTwithDuplicate, TobeDistinct);

//Following function will return Distinct records for Name, City and State column.
public static DataTable GetDistinctRecords(DataTable dt, string[] Columns)
{
    DataTable dtUniqRecords = new DataTable();
    dtUniqRecords = dt.DefaultView.ToTable(true, Columns);
    return dtUniqRecords;
}

@Viswa Teja Kuncham 2015-12-02 12:20:05

DataTable dt = new DataTable("EMPLOYEE_LIST");

DataColumn eeCode = dt.Columns.Add("EMPLOYEE_CODE", typeof(String));
DataColumn taxYear = dt.Columns.Add("TAX_YEAR", typeof(String));
DataColumn intData = dt.Columns.Add("INT_DATA", typeof(int));
DataColumn textData = dt.Columns.Add("TEXT_DATA", typeof(String));

dt.PrimaryKey = new DataColumn[] { eeCode, taxYear };

It filters data table with eecode and taxyear combinedly considered as unique

@Rahul 2011-06-29 08:48:40

Following single line of code will avoid the duplicate rows of a DataTable:

dataTable.DefaultView.ToTable(true, "employeeid");

Where:

  • first parameter in ToTable() is a boolean which indicates whether you want distinct rows or not.

  • second parameter in the ToTable() is the column name based on which we have to select distinct rows. Only these columns will be in the returned datatable.

The same can be done from a DataSet, by accessing a specific DataTable:

dataSet.Tables["Employee"].DefaultView.ToTable(true, "employeeid");

@Ian Boyd 2012-09-06 13:27:48

i like this answer the most, as it points to the DefaultView property of a DataTable.

@Rachel 2013-02-01 13:42:38

I like it best because it explains the code used :)

@Lijo 2016-11-01 13:41:19

What if I need to get distinct based on two columns?

@Kristen Hammack 2018-08-23 20:48:26

@Lijo, the ToTable(boolean, params string[] columnNames) method allows for multiple columns to be specified.

@onerkaya 2013-04-06 11:57:13

You can use like that:

data is DataTable

data.DefaultView.ToTable(true, "Id", "Name", "Role", "DC1", "DC2", "DC3", "DC4", "DC5", "DC6", "DC7");  

but performance will be down. try to use below code:

data.AsEnumerable().Distinct(System.Data.DataRowComparer.Default).ToList();  

For Performance ; http://onerkaya.blogspot.com/2013/01/distinct-dataviewtotable-vs-linq.html

@ces2601 2012-06-05 15:37:02

var distinctRows = (from DataRow dRow in dtInventory.Rows
                                select dRow["column_name"] ).Distinct();

var distinctRows = (from DataRow dRow in dtInventory.Rows
                                select dRow["col1"], dRow["col2"].. ).Distinct();

@Urik 2013-03-10 14:29:20

@Adi Lester: maybe select new { col1 = dRow["col1"], col2 = dRow["col2"], ...} ).Distinct(); is more correct?

@pat capozzi 2013-04-12 22:03:17

When you just have a List<DataRow> you can do this: var test = (from DataRow dRow in vm.LiveAssets select dRow["manname"]).Distinct();

@Alan Baljeu 2018-10-13 17:34:31

The first line works. The second, as Urik points out doesn't, but Urik's also does not work as Distinct() will not find equality when doing object-compare on anonymous types.

@Dylan 2012-09-04 07:36:43

var ValuetoReturn = (from Rows in YourDataTable.AsEnumerable()
select Rows["ColumnName"]).Distinct().ToList();

@Zain Ali 2011-07-29 05:51:47

With LINQ (.NET 3.5, C# 3)

var distinctNames = ( from row in DataTable.AsEnumerable()
 select row.Field<string>("Name")).Distinct();

 foreach (var name in distinctNames ) { Console.WriteLine(name); }

@Ravedave 2009-08-26 19:40:02

To improve the above answer: The ToTable function on dataview has a "distinct" flag.

//This will filter all records to be distinct
dt = dt.DefaultView.ToTable(true);

@proudgeekdad 2010-10-15 17:17:38

This doesn't appear to work. There is only one overload with a distinct Boolean parameter in it and it requires the parameter array. I think this will just return a table called "True" without any DISTINCT applied.

@SetFreeByTruth 2013-11-22 22:24:42

+1 This actually does work (at least in .NET 4.5). If you specify the Boolean value "True" as the only parameter, it performs a DISTINCT on all columns in the DataView.

@Thomas Levesque 2009-07-29 12:37:51

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);

@Charmie 2013-05-14 08:20:02

@User7291 2013-12-05 09:01:49

if i have 2 columns "mo" and "name" i need to get the distinct "mo" but not the distinct "name" but i need to keep the column "name" in my datatable what shall i do?

@Thomas Levesque 2013-12-05 09:14:13

@JocelyneElKhoury, that doesn't really make sense... which value of "name" will you keep then?

@User7291 2013-12-05 09:16:30

@ThomasLevesque it does not matter which one...let's say i need to keep the first value of the name

@Thomas Levesque 2013-12-05 09:39:48

OK, then you need grouping, not distinct. You could do it with Linq to DataSet: table.AsEnumerable().GroupBy(row => row.Field<int>("mo")).Select(group => group.First()).CopyToDataTable()

@Ovis 2014-07-03 14:08:01

Thanks Thomas, that was exactly what i needed

@brian 2014-10-23 07:19:15

Perfectooo, mani thanks thomas

@Alex 2016-11-15 18:52:39

You sir, are awesome!

@Hilal Al-Rajhi 2017-07-01 10:22:30

How come its not working with me!

@Martin Moser 2009-07-29 10:14:51

DataTable dt = new DataTable();
dt.Columns.Add("IntValue", typeof(int));
dt.Columns.Add("StringValue", typeof(string));
dt.Rows.Add(1, "1");
dt.Rows.Add(1, "1");
dt.Rows.Add(1, "1");
dt.Rows.Add(2, "2");
dt.Rows.Add(2, "2");

var x = (from r in dt.AsEnumerable()
        select r["IntValue"]).Distinct().ToList();

@Madi D. 2009-07-29 10:14:22

sthing like ?

SELECT DISTINCT .... FROM table WHERE condition

http://www.felixgers.de/teaching/sql/sql_distinct.html

note: Homework question ? and god bless google..

http://www.google.com/search?hl=en&rlz=1C1GGLS_enJO330JO333&q=c%23+selecting+distinct+values+from+table&aq=f&oq=&aqi=

@Madi D. 2009-10-22 08:16:32

to whomever downvoted me :S,, obviously the question was modified after my answer ?? (answer 10:15, question edited on 12:15 ) oh well.. thx for ur ignorance :)

@aggaton 2016-03-02 21:49:26

OP asks for how to select distinct rows in a C# ado.net environment, not in an actual database.

Related Questions

Sponsored Content

5 Answered Questions

[SOLVED] How do I (or can I) SELECT DISTINCT on multiple columns?

10 Answered Questions

[SOLVED] IEnumerable vs List - What to Use? How do they work?

5 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

7 Answered Questions

[SOLVED] SELECT DISTINCT on one column

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

17 Answered Questions

4 Answered Questions

[SOLVED] Select distinct using linq

  • 2013-10-16 14:33:50
  • Anoop Joshi
  • 591108 View
  • 222 Score
  • 4 Answer
  • Tags:   c# linq list class

7 Answered Questions

[SOLVED] Select unique or distinct values from a list in UNIX shell script

5 Answered Questions

Sponsored Content