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.