How to run a query for each entry in an array?

Question

I am trying to use LINQ to SQL to query corresponding data based on the values in my array, but am having issues. I was reading these threads, but am not sure if they are actually what I'm trying to do, and if they are how to implement them:

LINQ equivalent of foreach for IEnumerable<T>

Linq style "For Each"

I have my Array of strings 'lines[]', how can I run the following query for each entry, and store the results in a way that allows me to output them in a coherent manner. Again here is a sample of my array: Example: Z1234 Z2345 ZAF38383

//some non working code
List<string> results = new List<string>();

var thisQuery = from c in myContext.SpecificTable
where c.itemNumber == (foreach (string i in lines))
select c;

foreach (var result in thisQuery)
{
    results.Add(result);
}

The list creation is fine, and the write to the list would be ok too I think, but I can't figure out how to run the query for each item int he Array?

Each entry in my array begins with Z and then will contain any arrangement of alpha-numeric characters if it matters. Example: Z3333

Each entry in the array corresponds to an entry in a table in my database 'SpecificTable'. I want to return everything pertaining to this value, in that table, so I can then output the specifics of that data.

Example: I want to begin by quering Z1234, and when Z1234 is found in 'SpecificTable' I want to be able to output the various details like this:

foreach (var res in thisQuery)
{
   //each result from the query (total of 3 from the example) will now show their Description in a messagebox.
   MessageBox.Show("Description:" + res.Description.ToString());
 }

By using the loop I hope to be able to create a list of all results based on the initial array, and output their corresponding various values such as the 'Description'.

If this is still not enough info, please let me know what I can provide to be more clear.

Answer 1

Try:

This will loop the rows in myContext.SpecificTable and creates an innerloop to check if there is a match within the lines.

var thisQuery = from c in myContext.SpecificTable
                from i in lines
                where c.itemNumber == i
                select c;

or this:

This will do the same, only the second 'iterator' is done within the contains.

var thisQuery = from c in myContext.SpecificTable
                where  lines.Contains(c.itemNumber)
                select c;

I think it's best to put the db query in the outerloop, because you don't want a 'tablescan' for each item in lines.

So it will do a 'tablescan' only ones, and try to find a match in the lines.

I think it will speed up if you try something like this:

var linesHashSet = new HashSet<string>(lines);

var thisQuery = from c in myContext.SpecificTable
                where  linesHashSet.Contains(c.itemNumber)
                select c;

Here it will use in the second iteration a hashset (indexed list)

---

Update:

It is possible to iterate the rows clientside, like this:

var thisQuery = from c in myContext.SpecificTable.ToArray()
                where  lines.Contains(c.itemNumber)
                select c;

But this will have a performance inpact. I suggest you try the middle one.

Answer 2

Not sure if I understand your question correctly, but you want to join the entries in SpecificTable with the "lines" array, and output the results as a list?

var results = myContext.SpecificTable.Select(c => lines.Contains(c.ItemNumber)).ToList();
foreach(var result in results)
{
    Console.WriteLine(string.format("{0}:{1}", "Description", result.Description));
}

In case you want specific properties only, you can return a list of anonymous objects.

var results = myContext.SpecificTable.Where(c => lines.Contains(c.ItemNumber)).Select(c => new {c.ItemNumber, c.Description}).ToList();
foreach(var result in results)
{
    Console.WriteLine(string.format("{0}:{1}", "Description", result.Description));
}