Why does my dictionary search give an SQL timeout error?

Question

Part of my application uses the names of the roads that are stored in the SQL database. The application will access each name record at least once, and most of them will be available several times. So I decided to load the name data into the dictionary, and then looked at it to reduce reading the database.

The road name table has ~ 3 million entries, 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); } 

Running as expected. Stopping the code at this point and hovering the mouse over the roadNames variable in Visual Studio shows that the dictionary contains the expected key-value pairs.

However, when I move to the next line later in the program

 DbRoadName roadName = roadNames[lookupId]; 

the program stops with the following exception

.Net SqlClient data provider: timed out.

As I understand it, the ToDictionary() method should lead to the execution of the database query at this point, so why am I getting an SQL timeout error when searching in a dictionary?

Update: I “fixed” the problem by replacing

 DbRoadName roadName = roadNames[lookupId]; 

using the TryGetValue statement. However, I'm still wondering why the in-memory dictionary throws an SQL exception.

Answer 1

to avoid SQL timeout, I would not load the storage model in my dictionary. Instead, load the data as a Model with the necessary properties, for example:

 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 it help?