DataTable / Row conflicts with Dictonary

[Signalman]

I currently have a procedure that reads the contents of a two Database (SQL read with conditions ) and Fills a two Dataset / Data table with the results.

One of these is then added to A dictionary to provide a quick lookup.

I then loop through (for each row) the other data tables (the smaller) looking up information on the Dictionary (think I am it a LINQ query) to find a specific record.

The issue is that when I try and and use / reference the contents of the LINQ query, it generates an error - Can not be indexed as there is no default property. I also tried adding .tolist to the LINQ query, but that gave an error of 'UserRow is not declared'

    Dim Lookedup = UserDictionary.Where(Function(x) x.Key.Contains(RecordKeyToFind)) _
.Select(Function(x) x.Value)    '.ToList()

If Lookedup.Count > 1 Then
    For Each LookedupRow In Lookedup
        MsgBox(LookedupRow("StartDate"))
    Next
End If

when I debug and look at the contents of the LINQ query, it correctly shows the field names, so I am unsure why I can not reference it. Note the Dictonary result is a class containing numerous fields

Whist this may seem a strange way of going about things, this is a simplified 'example', as the main database contains may 10000's of records and is to slow to look up each record on the actual database one record a time

How do I reference the result of the LINQ query which contains a class of fields as I want the contents of the individual field ?

[jmcilhinney]

You should have already told us what the data types of x.Key and x.Value are. There seems to be an implication that the values are DataRow objects but the error message seems to suggest otherwise. Maybe you could provide a complete example that we can run and demonstrates the issue, using canned data. Without being able to run that code for ourselves in a debugger, your description seems to lack critical details to enable us to diagnose the issue. Either that or I'm just missing something obvious.

[Shaggy Hiker]

It looks like you are searching through a dictionary object. That isn't an efficient thing to do. As far as I know, it is neither better nor worse than searching through a datatable directly. Dictionaries are very fast if you are retrieving an item by the key, but if all you are doing is searching through the dictionary, then you aren't gaining anything.

[Signalman]

Thanks Shaggy, are you suggesting that the code (Dim……LINQ query) is not making use of the dictionary index, (I assumed it was, but I am now wondering) but just doing a search of the dictionary ?If this is the case, then there is no point in creating a dictionary from the data table.

JMC, will post the code as soon as I have the opportunity.

[Shaggy Hiker]

Yeah, it certainly looks like that. You are going through the keys looking for the one that matches. That iteration is no faster in a Dictionary than in any other collection.

Looking up key X in a Dictionary is going to be faster than iterating through rows in a datatable, items in an array, or any such collection, but not if you have to iterate through the keys in the Dictionary to find X. That means that Dictionaries are not ALWAYS going to provide any benefit. If you sometimes have to iterate the keys to find X, and other times can just get MyDictionary(x), then using a dictionary will be advantageous. The more often you can write MyDictionary(x) rather than iterating through the keys, the more advantageous the dictionary will be. Conversely, the more often you iterate, the less advantageous.

For that reason, you might think a bit more about what you are actually trying to get. For example, could you make the key simply whatever RecordKeyToGet is? I've written dictionaries where I used one field from a datatable as the key, and a datarow from the same table as the value, though I would only do that when I felt that I would be doing a simple lookup most of the time.