Part of my application uses road names which are stored in a SQL database. The application will access every name record at least once and most will be accessed multiple times. So I decided to load the names data into a dictionary and then look-up against that to reduce the database reads.
The road names table has ~3 million records and I use the following Linq-to-SQL to load it into memory;
Dictionary<String, DbRoadName> roadNames;
using (RoutingDataContext dc = new RoutingDataContext(connectionString))
{
roadNames = dc.DbRoadNames.ToDictionary(x => x.RoadId);
}
This executes as expected. Stopping the code at this point and putting the mouse over the roadNames variable in Visual Studio shows that the dictionary appears to contain the expected key-value pairs.
However, when I get to the following line later in the program
DbRoadName roadName = roadNames[lookupId];
the program stops with the following exception
.Net SqlClient Data Provider: Timeout expired.
As I understand it, the ToDictionary() method should cause the database query to execute at that point, so why am I getting a SQL timeout error at the dictionary lookup?
Update: I 'fixed' the problem by replacing
DbRoadName roadName = roadNames[lookupId];
with a TryGetValue statement. However, I'm still interested in why the in-memory dictionary was producing a SQL exception.
