In C#, is there out-of-the-box way to build a 3-way lookup table?

Question

I have an in-memory "table" that might looks something like this:

Favorite#  Name        Profession
---------  ----------  ------------------
3          Names.Adam  Profession.Baker
9          Names.Bob   Profession.Teacher
7          Names.Carl  Profession.Coder
7          Names.Dave  Profession.Miner
5          Names.Fred  Profession.Teacher

And what I want to do, is do quick and efficient lookups, using any of the 3 fields. In other words, I want:

• myTable[3] and myTable[Names.Adam] and myTable[Professions.Baker] to all return {3,Names.Adam,Profession.Baker}
• myTable[Profession.Teacher] to return both {9,Names.Bob,Profession.Teacher} and {5,Names.Fred,Profession.Teacher}.

The table is built during runtime, according to the actions of the user, and cannot be stored in a database since it is used in sections in which database connectivity cannot be guaranteed.

Right now, I "simply" (hah!) store this using 3 uber-Dictionaries, each keyed using one of the columns (FavoriteNumber, Name, Profession), and each value in the uber-Dictionaries holding 2 Dictionaries which are themselves keyed with each of the remaining columns (so the values in the "Name" uber-dictionary are of the type Dictionary<FavoriteNumber,Profession[]> and Dictionary<Profession, FavoriteNumber[]>

This requires 2 lookups in 2 Dictionaries, and another traverse of an array (which usually holds 1 or 2 elements.)

Can anyone suggest a better way to do this? I don't mind spending extra memory, since the table is likely to be small (no more than 20 entries) but I'm willing to sacrifice a little CPU to make it more readily maintainable code...

Answer 1

Not really however using a dictionary, but if you create a collection of classes like this

class Person {
    public int FavoriteNumber;
    public string Name;
    public string Profession;
}

you can use LINQ to search the collections.

IList<Person> people = /* my collection */;
var selectedPeople = people.Where(p => p.FavoriteNumber = 3);
var selectedPeople2 = people.Where(p => p.Name == "Bob");
var selectedPeople3 = people.Where(p => p.Profession = "Teacher");

or if you prefer the normal LINQ syntax

var selectedPeople4 = from p in people
                      where p.Name == "Bob"
                      select p;

Each of these selectedPeople variables will be typed as IEnumerable<Person> and you can use a loop to search through them.

Answer 2

I think the way to do this is to write your own object that has

public ICollection<Record> this[int] { get; }
public ICollection<Record> this[Profession] { get; }
public ICollection<Record> this[Names] { get; }

where record is a class that holds your elements.

Internally, you keep a List and each indexer does List.FindAll() to get what you need.

Answer 3

Could you use an sqlite database as the backing? With sqlite you even have the option of building an in-memory db.

Answer 4

Nothing out-of-the-box (except perhaps a DataTable). Nevertheless, it can be accomplished in a more simple way that what you've got:

Create a class to hold the data:

class PersonData {
   public int FavoriteNumber;
   public string Name;
   public string Profession;
}

Then keep 3 dictionaries that point to the same reference:

PersonData personData = new PersonData();
Dictionary<int, PersonData> ...;
Dictionary<string, PersonData> ...;
Dictionary<string, PersonData> ...;

I'd recommend encapsulating all of this into a facade class that hides the implementation details.

Answer 5

For 20 rows, just use linear scanning - it will be the most efficient in every way.

For larger sets; hzere's an approach using LINQ's ToLookup and delayed indexing:

public enum Profession {
    Baker, Teacher, Coder, Miner
}
public class Record {
    public int FavoriteNumber {get;set;}
    public string Name {get;set;}
    public Profession Profession {get;set;}
}
class Table : Collection<Record>
{
    protected void Rebuild()
    {
        indexName = null;
        indexNumber = null;
        indexProfession = null;
    }
    protected override void ClearItems()
    {
        base.ClearItems();
        Rebuild();
    }
    protected override void InsertItem(int index, Record item)
    {
        base.InsertItem(index, item);
        Rebuild();
    }
    protected override void RemoveItem(int index)
    {
        base.RemoveItem(index);
        Rebuild();
    }
    protected override void SetItem(int index, Record item)
    {
        base.SetItem(index, item);
        Rebuild();
    }
    ILookup<int, Record> indexNumber;
    ILookup<string, Record> indexName;
    ILookup<Profession, Record> indexProfession;
    protected ILookup<int, Record> IndexNumber {
        get {
            if (indexNumber == null) indexNumber = this.ToLookup(x=>x.FavoriteNumber);
            return indexNumber;
        }
    }
    protected ILookup<string, Record> IndexName {
        get {
            if (indexName == null) indexName = this.ToLookup(x=>x.Name);
            return indexName;
        }
    }
    protected ILookup<Profession, Record> IndexProfession {
        get {
            if (indexProfession == null) indexProfession = this.ToLookup(x=>x.Profession);
            return indexProfession;
        }
    }
    public IEnumerable<Record> Find(int favoriteNumber) { return IndexNumber[favoriteNumber]; }
    public IEnumerable<Record> Find(string name) { return IndexName[name]; }
    public IEnumerable<Record> Find(Profession profession) { return IndexProfession[profession]; }
}