Answered by:
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
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.- Edited by Zhanglong WuMicrosoft contingent staff Tuesday, November 29, 2016 12:45 AM
- Marked as answer by PaulCr1251 Friday, December 2, 2016 12:35 PM
Monday, November 28, 2016 9:58 AM
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.- Edited by Zhanglong WuMicrosoft contingent staff Tuesday, November 29, 2016 12:45 AM
- Marked as answer by PaulCr1251 Friday, December 2, 2016 12:35 PM
Monday, November 28, 2016 9:58 AM -
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 -
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