Exploring LINQ #3

LINQ keeps amazing me in its power and beauty. So much that for each problem I have to solve (coding problem that is)  I find myself wondering can this be done with LINQ, and how.

I am working on a rewrite of an old application to C#. For some reason the original designers had put some ‘model’ data into a none relational database. Instead of making a good relational data model the data has been put as follows (simplified record from the database) :

Option1 = “1;2;3”
Option2 = “A;B”
Value = 1

In other words foreach possible combination of option1 and option2 there is a value. (1-A-1, 1-B-1, 2-A-1 etc.) I would prefer a relational set up, but to honor the past this will have to stay that way. For use in our program it  is required to have all possible combinations into a single flat list which we then can query with LINQ.
Of course it easy to construct some code that would knock this down into a flat list though I don’t think any will be as elegant and as fast to produce as this LINQified solution.

Suppose we read the raw data from the database into a list with raw objects looking like this:

List<Raw> rawList = new List<Raw>();
rawList.Add(new Raw() { Option1 = "1;2;3", 
                        Option2 = "A", Value = 1 });
rawList.Add(new Raw() { Option1 = "4;5", 
                        Option2 = "A;B", Value = 2 });
rawList.Add(new Raw() { Option1 = "1;2;3", 
                        Option2 = "C", Value = 3 });

Using LINQ it is easy to combine the original raw list with the “;” splitted list (arrays in fact) from the fields Option1 and Option2. From that a flat list can be constructed which holds all the combinations:

List<Raw> flatList = (from n in rawList
                      from opt1 in n.Option1.Split(';')
                      from opt2 in n.Option2.Split(';')
                      select new Raw
                      {
                        Option1 = opt1,
                        Option2 = opt2,
                        Value = n.Value
                      }).ToList();
flatList.ForEach((n) => Console.WriteLine(n.Option1 + 
                        "-" + n.Option2 + 
                        "-" + n.Value.ToString()));

Resulting in a ‘flat’ list:

1-A-1
2-A-1
3-A-1
4-A-2
4-B-2
5-A-2
5-B-2
1-C-3
2-C-3
3-C-3

Querying this list is now child’s play:

int Result = (from n in flatList
              where (n.Option1 == "4") && (n.Option2 == "B")
              select n.Value).FirstOrDefault();