Silentbob Silentbob - 1 month ago 7
Vb.net Question

Working with the data from LinQ SQL query

Using VS 2013 (VB) and SQL server 2016.

I have linQ query that returns two columns from a database. The query is as follows.

Dim val = (From value In db.ngc_flowTypes
Where value.defaultValue IsNot Nothing
Select value.flowName, value.defaultValue)


The data it returns is a as follows.

result set from sql table

I want to iterate through each row of the results and pass the values to certain variables. A ForEach statement doesnt seem to work as it just runs through once. I am sure this must be easy but I ont quite understand it. Am I getting the data returned in the best way via my query? Can I transpose the data to a data table in VB? so I can work with it easier?

The end result I want is string for each flow name with its corresponding default value (along with some other text). So something like this.

dim strsubmission as string = flowName + " has a value of " + defaultValue

Answer

Use ToDictionary.

Dim val = (From value In db.ngc_flowTypes
           Where value.defaultValue IsNot Nothing
           Select value).ToDictionary(Function(key) key.flowName, 
                                      Function(value) value.defaultValue)

This will actually execute the SQL of the linq on the database (approx. Select * From ngc_flowTypes Where defaultValue Is Not NULL), traverse each record into a key/value pair (flowName, defaultValue) and put it into a in-memory dictionary variable (val).

After that you can do whatever you like with the dictionary.

 For Each flowName In val.Keys
      Console.WriteLine("{0} has a value of {1}", flowName, val(flowName))
 Next

Edit: This will only work as long flowName is unique in table ngc_flowTypes