Aggregate distinct count in LINQ - VB.NET

Question

I have a data that is comming from the database and going into PagedDataSource. The data is from a query which pull some 10 records with some 20 column. Based on one varchar column name `source', I want to find unique instances of the source. This is a very basic query. In SQL I would do this:

select count(distinct source) from SourceTable

I have tried the following, which does give count but not distinct (or unique) count.

Dim query = Aggregate source In pagedDS
            Into Count()

I do not know how to select column in LINQ queries and how to apply aggregate by just one column. I need this in VB.NET but a C# solution should be close enough.

Servy · Answer 1 · 2013-06-06T18:39:08.510

3

Since PagedDataSource doesn't implement IEnumerable<T>, just IEnumerable, you need to first use Cast to cast the sequence to a sequence of the proper type. You'll need to know what the actual type of the objects in the sequence are as you haven't mentioned what it is here.

Then just apply each operation one after the other:

(In C#:)

var query = table.Cast<YourTypeGoesHere>()
    .Select(item => item.Column)
    .Distinct()
    .Count();

edited Jun 06 '13 at 18:39

answered Jun 06 '13 at 18:25

Servy

202,030
26
332
449

@Nick This is C# code, not VB code, but the translation should be straightforward enough. You explicitly stated you didn't care whether the answers were C# or VB. – Servy Jun 06 '13 at 18:27
I need it working in VB.NET but I can work on translating it to VB if VB solution is not available. – TheTechGuy Jun 06 '13 at 18:28
In VB, it's the following (and use 'Option Infer On'): Dim query = table.Select(Function(item) item.Column).Distinct().Count() – Dave Doknjas Jun 06 '13 at 18:31
I guess since I am using PagedDataSource, the above query does not exactly work. I know it will work with table but not with PagedDataSource. Again I will consider changing PagedDataSource to a DataTable if required. – TheTechGuy Jun 06 '13 at 18:34
@Nick See edit; you'll need to use `Cast` to get an `IEnumerable` before you can use most of the LINQ methods. – Servy Jun 06 '13 at 18:39
Thanks @Servy that helps. I am working on that line. Will probably fetch data in a DataTable to get the count. But count does work by the way, don't know how to get distinct. – TheTechGuy Jun 06 '13 at 18:40

score 3 · Answer 2 · answered Jun 06 '13 at 19:08

I ditched PageDataSource because I could not use LINQ with it in the fashion I wanted to. I introduced a DataTable instead just for this purpose and ran my query on DataTable which worked fine. Here is the code

    Dim dv As New System.Data.DataView
    Dim dt As New System.Data.DataTable
    dv = osReviews.Select(DataSourceSelectArguments.Empty)
    dt = dv.ToTable()


    Dim query = (From row In dt
                 Select row("Source") Distinct).Count()

score 1 · Answer 3 · answered Jun 06 '13 at 18:32

1

Since we have the Distinct keyword in Visual Basic, you could use this query:

Dim query = (From b In Locales
             Select b.Id Distinct).Count()

answered Jun 06 '13 at 18:32

Christoffer Mansfield

793
4
11

Aggregate distinct count in LINQ - VB.NET

3 Answers3