I'm trying to get one random row from each group of rows. I'm trying to use LINQ, but I'm not sure if that's the right approach. I'd like a Dictionary of Key/Name pairs.
My table is as such:
AnswerGroup AnswerKey AnswerName --------------------------------------------- 1 1 Yes 1 2 No 2 1 Never 2 2 A little bit 2 3 Mostly 2 4 Always 3 1 White 3 2 African American 3 3 Hispanic 3 4 Asian or Pacific Islander
For each AnswerGroup I need to choose a random Key/Name pair.
I have the beginnings of a LINQ query, but frankly I'm lost as I don't understand LINQ grouping and how to add an Enumerable.Take(1) to the group.
Dim answerGroup As String = "AnswerGroup" Dim answerKey As String = "AnswerKey" Dim answerName As String = "AnswerName" Dim query = _ From rows As DataRow In surveyAnswerKeys.Rows _ Order By rows(answerGroup) _ Group By questionSortKey = rows(answerGroup) _ Into questionGroups = Group
Any help would be appreciated. Thanks!
Edit: I can expand the following query in the debugger to see an In Memory Query that produces a series of DataRows. When I hover over questionGroups it says it's a IEnumerable(Of Object). When I try to run that query into a list or DataTable I get error:
"Public member 'ToTable' on type 'WhereSelectEnumerableIterator(Of VB$AnonymousType_0(Of Object,IEnumerable(Of Object)),Object)' not found."
Dim answerGroup As String = "QuestionSortKey" Dim answerNo As String = "AnswerNo" Dim surveyDefinitionNo As String = "Pk_SurveyDefinitionNo" Dim query = _ From rows In surveyAnswerKeys.Rows _ Where rows(answerNo) IsNot Nothing _ Order By Guid.NewGuid() _ Group By questionSortKey = rows(answerGroup) _ Into questionGroups = Group _ Select questionGroups.First() Dim randomAnswerNos As DataTable = query.ToTable