SQL LINQ Query: Selecting specific column

Question

Today I am needing to write LINQ queries in VB.net to a database table, but am new to SQL/LINQ. This function below is meant to fill a list of strings with all of the possible "Questions" in the database table that match the QuestionType.

However, I only want to select one single column, the QuestionText column, and not all of the data, whenever I have a match.

Public Shared Function RetrieveQuestions(ByVal QuestionType) As List(Of String)
    Dim db As New DBDataContext()
    db.CommandTimeout = 300
    Dim ListOfQuestions As List(Of String) = New List(Of String)
    While True
        Dim questionList As List(Of Question) = db.Questions.ToList
        Dim question As List(Of String) = (From q As Question In questionList Where q.FormType = QuestionType Select q.QuestionText).ToList
        Dim i As List(Of String) = question
        If (question IsNot Nothing) Then
            ListOfQuestions(ListOfQuestions.Count) = i.QuestionText //ERROR
        Else
            Exit While
        End If
    End While
    Return ListOfQuestions
End Function

In the function above i am encountering an error when trying to update my list with the new QuestionText. "QuestionText is not a member of System.Collections.Generic.List(Of String)". QuestionText is defined as a varchar in my SQL database, so I know that it is definitely a string. I am not trying to set QuestionText to a list of strings, but rather add it to the end of a list of strings.

Community · Accepted Answer · 2017-05-23 10:33:22Z

1

Direct answer: you'd need to put the whole If (question IsNot Nothing) Then block in a loop like For Each. As the compiler correctly informs - the i variable holds the whole list, not one of its items. Perhaps you forgot you left the LINQ query?

A better solution: I believe you could just use AndAlso q.QuestionText IsNot Nothing - it spares you the need to allocate a new list and to fill it one by one - the following code should do the trick.

Public Shared Function RetrieveQuestions(ByVal QuestionType) As List(Of String)
    Dim db As New DBDataContext()
    db.CommandTimeout = 300

    Dim ListOfQuestions As List(Of String) = (
            From q As Question In db.Questions.ToList
            Where
                    q.FormType = QuestionType
                    AndAlso q.QuestionText IsNot Nothing

            Select q.QuestionText
    ).ToList

    Return ListOfQuestions
End Function

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered May 13, 2014 at 19:06

Khub

1031 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL LINQ Query: Selecting specific column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related