Retrieve distinct values from datatable using linq vb.net

Question

I am trying to retrieve all of the distinct values from a particular column in a datatable. The column name in the datatable is "Count". I have 2240 rows and I have 6 distinct values in the "Count" column. The problem is, when I execute the following code, it is giving me the number of rows rather than the 6 distinct values.

Dim counts = (From row In loadedData
Select row.Item("Count")).Distinct()
For Each i In counts
    MsgBox(i)
Next

How can I modify this to retrieve the 6 distinct values, rather than it giving me the total number of rows?

FYI - The values in this datatable are all unique... except that of the "Count" column. I believe this is why it is returning the 2240 rows, instead of just the 6 unique count values. — John Janssen
– John Janssen, Commented Oct 8, 2013 at 12:41

Tim Schmelter · Accepted Answer · 2013-10-08 12:42:41Z

11

You just have to select the column and use Enumerable.Distinct:

Dim distinctCounts As IEnumerable(Of Int32) = loadedData.AsEnumerable().
    Select(Function(row) row.Field(Of Int32)("Count")).
    Distinct()

In query syntax(i didn't know that even Distinct is supported directly in VB.NET):

distinctCounts = From row In loadedData
                 Select row.Field(Of Int32)("Count")
                 Distinct

answered Oct 8, 2013 at 12:42

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

2 Comments

John Janssen Over a year ago

I tried to use your code you posted, but I don't think you read my last comment. All 2240 are unique, however I am just trying to get the unique count column. "Select distinct count from datatable" - essentially.

Tim Schmelter Over a year ago

@JohnJanssen: That is exactly the Linq-To-DataSet equivalent from your posted sql query. It returns only the distinct counts from the table. So if the table contains 2240 rows and the Count column contains 6 different numbers(spread over all rows), the query returns only 6 different numbers.

Chinthaka · Accepted Answer · 2015-09-30 04:06:29Z

6

You can use ToTable(distinct As Boolean, ParamArray columnNames As String()) method for this.

loadedData.DefaultView.ToTable(True, "Count")

This will return distinct Users for you. You can add multiple column names if you want.

Here is the msdn documentation. https://msdn.microsoft.com/en-us/library/wec2b2e6(v=vs.110).aspx

edited Sep 30, 2015 at 4:06

answered Sep 29, 2015 at 11:39

Chinthaka

3434 silver badges13 bronze badges

1 Comment

Rick Smith Over a year ago

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this".

Dhaval Tejlavwala · Accepted Answer · 2014-10-17 05:07:07Z

0

you can also apply this logic if you want , first sort datatable through columName , then apply this logic

    dtRecords.DefaultView.Sort = "columnName"
    dtRecords = dtRecords.DefaultView.ToTable
    Dim totalRecords As Integer = 0
    Dim thNameStr As String = filter(dtRecords, "columnName", totalRecords )

Public Shared Function filter(ByVal dtRecords As DataTable, ByVal columnName As String, ByRef totalRecords As Integer) As String
            Dim FilterStr As String = ""
            Dim eachTotal As Integer = 0
            totalRecords = 0
            Dim lastName As String = ""
            For rCount = 0 To dtRecords.Rows.Count - 1
                If lastName <> "" And lastName <> dtRecords.Rows(rCount)("" & columnName) Then
                    FilterStr &= lastName & " - [" & eachTotal & "]"
                    eachTotal = 0
                    totalRecords += 1
                End If
                lastName = dtRecords.Rows(rCount)("" & columnName)
                eachTotal += 1
            Next
            FilterStr &= lastName & " - [" & eachTotal & "]"
            totalRecords += 1
            Return FilterStr 
        End Function

answered Oct 17, 2014 at 5:07

Dhaval Tejlavwala

3794 silver badges17 bronze badges

1 Comment

John Janssen Over a year ago

A little bloated. That just seems like a lot of code to do the same thing that I can do in 3 lines of code with LINQ. The code doesn't seem wrong at all though.

Collectives™ on Stack Overflow

Retrieve distinct values from datatable using linq vb.net

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related