CODEWITHSUNDEEP
vb.netlinq
Intricate
Intricate

Reputation: 117

get distinct count and name using linq from dataset table

I'm fairly new to LINQ using VB.Net. I got to know that its quite fast in fetching results.

I have a dataset with a table and want to retrieve distinct values with the count

The data is represented by (around 50,000 lines of csv)

p1.pdf

e3.xls

d1.doc

p2.pdf

e2.xls

d2.doc

p1.pdf

e3.xls

d2.doc

d4.doc

would like to have like this

p1.pdf - 2
p2.pdf - 1

e3.xls - 2
e2.xls - 1

d2.doc - 2
d1.doc - 1
d4.doc - 1

I have tried something like this

Dim Filtered = From row in ds.tables("AuditLog").AsEnumerables()
               Where (row.Field(Of String)("DocName") Like "*.pdf")

Dim dt as New Datatable
dt = Filtered.CopyToDataTable

This gives the list of all pdfs, and I could use the dataview to get the count, but if there is a faster way in getting the count, it would be great

Please help me if possible

Thanks for your patience

Upvotes: 0

Views: 1614

Answers (1)

Daniel Drews
Daniel Drews

Reputation: 101

Try this

    Dim dt As DataTable
    Dim dr As DataRow
    Dim nameCoulumn As DataColumn

    dt = New DataTable("files")
    nameCoulumn = New DataColumn("Name", Type.GetType("System.String"))
    dt.Columns.Add(nameCoulumn)

    dr = dt.NewRow()
    dr("Name") = "p1.pdf"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "e3.xls"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "p2.pdf"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("Name") = "p1.pdf"
    dt.Rows.Add(dr)

    Dim query = From row In dt.AsEnumerable()
                Group row By name = row.Field(Of String)("Name") Into g = Group
    Select New With {
        Key name,
        .Count = g.Count()
        }

    For Each row In query
        Console.WriteLine(String.Format("{0} - {1}", row.name, row.Count.ToString()))
    Next

Upvotes: 3

Related Questions