Filter and Find from in-memory data sets

Question

I have several medium-sized data sets in-memory that I need to be able to filter and find information from quickly. The data sets are small enough that I don't want to take the performance hit of going to a database every time I need an entry but large enough that I really need to index the data somehow.

Currently, I'm using POCO objects with one or more dictionaries for indexing. This works excellent when I need to find something by a specific key, but sometimes that isn't the case. As an example, I often need to find an entry within a specific date-time-range. And sometimes I need the entry with the lowest price. Most often, queries look at a few simple keys and one or two other fields at the same time.

Are there any tools, products, libraries (targeting the .NET-framework) that can help me with this? Or do I need to pick up that big dusty old Algorithms book and start looking at search-trees?

An example:

Trip

• DepartureCode
• DestinationCode
• HotelCode
• RoomCode
• Date
• Price

I need the query to be something like "get me the least expensive Trip between 2010-03-09 and 2010-03-12 where DepartureCode=LAX DestinationCode=NYC"

Answer 1

How about the DataSet.Table("YourTable").Select() method?

Dim myRows() as DataRow = myDataSet.Tables("myTable").Select("Date>" & _
    myBeginDate & "AND Date<" & myEndDate)

EDIT: From MSDN

DataView Construction

The DataView builds an index for the data in the underlying DataTable when both the DataView is created, and when the Sort, RowFilter or RowStateFilter properties are modified. When creating a DataView object, use the DataView constructor that takes the Sort, RowFilter, and RowStateFilter values as constructor arguments (along with the underlying DataTable). The result is the index is built once. Creating an "empty" DataView and setting the Sort, RowFilter or RowStateFilter properties afterward results in the index being built at least twice.

So if you're wanting to index your DataSet, it looks like a DataView could provide that for you.

Answer 2

"Lowest price" and "specific date/time range" can both be handled using just a sorted collection and binary search. SortedList / SortedDictionary (or SortedSet if you're using .NET 4.0) probably do everything you need here, with only a fairly small amount of work.