Why does my dictionary lookup give a SQL timeout error?

Question

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.

Answer 1

to avoid the SQL timeout i would not load the Storage-Model in my dictionary. Instead load the data As Model with the necessary properties like this:

Dictionary<String, DbRoadNameModel> roadNameModelDictionary;
using (RoutingDataContext dc = new RoutingDataContext(connectionString))
{
    roadNames = dc.DbRoadNames
        .Select(roadName => new DbRoadNameModel(roadName.RoadId, roadName.Prop1, roadName.Prop2))
        .ToDictionary(x => x.RoadId);
}

Does this help?