none
LinQ Count(Distinct Columname) RRS feed

  • 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

    Friday, November 25, 2016 9:23 PM

Answers

  • 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 MSDNFSF@microsoft.com.


    Monday, November 28, 2016 9:58 AM
    Moderator

All replies

  • 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 MSDNFSF@microsoft.com.


    Monday, November 28, 2016 9:58 AM
    Moderator
  • 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

    Monday, November 28, 2016 12:51 PM
  • 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 MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 5:26 AM
    Moderator
  • 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

    Friday, December 2, 2016 12:57 PM