LinQ Count(Distinct Columname)

Question

I have a table that has several columns but the 1st 2 columns contain "repeats". What I would do in TSQL would be Select Col1 , Count(Distinct Col2) as items from tbl group by Col1.

I have the LinQ syntax to get col1 and counts of col2, but I need col1 and counts of distinct col2. I'm sure it possible 

        Dim OpCounts As IEnumerable(Of DataRow) = ds.Tables(0).AsEnumerable()
        Dim query = From ops In OpCounts _
                    Group By Part = ops.Field(Of String)("Part") Into g = Group _
                    Select New With _
                       { _
                           .Part = Part, _
                           .Items = g.Count(Function(ops) ops.Field(Of Integer)("OpNum")) _
                       }

---

Paul J. Ilacqua

Answer 1

HI Paul,

There is no query syntax for Distinct and Count like “Count(Distinct Columname)” in LINQ.  Try following code to  implementing this.

 

Dim query = From ops In OpCounts
                    Group By Part = ops.Field(Of String)("Part")
                    Into g = Group
                    Select New With {
                     .Part = Part,
                    .Items = (From t In g Select t.Field(Of Int32)("OpNum")).Distinct().Count()
                    }

             

Best Regard,

Cole Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Cole WU responded with the following but it does not show in this thread.

Dim query = From ops In OpCounts_

                    Group  By Part = ops.Field(Of String)("Part")_

                    Into g = Group _

                    Select New With {_

                     .Part = Part, _

                    .Items = (From t In t.OpNum).Distinct().Count() _

                    }

However I'm confused with the (from t in t.OpNum), where is the t reference coming from?

---

Paul J. Ilacqua

Answer 3

Hi PaulCr1251,

I have updated the reply, please check it.

Best regards,

Cole Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Cole, 

Thank you... it works fine, and I also understand the concept similar to a derived table. I'm assuming a "query" on a "query" using a join on a key will yield the same result. Tedious to get my mind around LINQ after working with TSQL forever. LINQ will help me offload some of the redundant processing of data with less latency on the server. Thanks Again

Paul

---

Paul J. Ilacqua