Retrieving a list, by iterating through a list

Question

I'm using VB.Net, MVC 5, EF 6, and Linq. I have a list of Integers (category attribute IDs). I need to create a second list of String (values). There will be one string for each integer.

I am currently accomplishing my task like this:

Function getValues(catAttIDs As List(Of Integer), itemID As Integer) As List(Of String)
    Dim db As New Model1
    Dim values = New List(Of String)

    For i As Integer = 0 To catAttIDs.Count - 1
        Dim catAttID = catAttIDs(i)

        Dim currentValue = (From row In db.tblEquipment_Attributes
                     Where row.Category_Attribute_Identifier = catAttID _
                     And row.Unique_Item_ID = itemID
                     Select row.Value).SingleOrDefault()

        values.Add(currentValue)
    Next

    Return values
End Function

I have a strong feeling that there is a better way to do this, but I have not been able to find the information I'm looking for.

I'm particularly interested in changing this code so that the database is called once for the list, instead of calling the database 5 or 6 times as I work my way through the list.

Answer 1

You're looking for the LINQ-equivalent of an IN clause in SQL. So something like this:

SELECT value FROM tblEquipment_Attributes 
WHERE Category_Attribute_Identifier IN (<list of integers>) 
AND Unique_Item_ID = itemID;

So what you could do is write your LINQ statement to see if the Category_Attribute_Identifier is in the list. Then your function will look something like this:

Function getValues(catAttIDs As List(Of Integer), itemID As Integer) As List(Of String)
    Dim db As New Model1

    Dim currentValues as List(Of String) = (From row In db.tblEquipment_Attributes
                     Where catAttIDs.Contains(row.Category_Attribute_Identifier) _
                     And row.Unique_Item_ID = itemID
                     Select row.Value).ToList()

    Return currentValues
End Function

Note that ToList will create a List<T>, where T is the type of the elements. As long as Value in your db is a varchar, it'll be a List.