Method to do a "join" when using LINQ in db4o?

Question

Whats the correct LINQ to navigate to the end leaf node of the following data structure?

Root ----Symbol (APPLE)                
   |          |------Day 1: Date: 2010-10-18, string "Apples on Thursday"
   |          |------Day 2: Date: 2010-10-19, string "Apples on Friday"
   |
   |
   |-----Symbol (PEAR)               
              |------Day 1: Date: 2010-10-18, string "Pears on Thursday"
              |------Day 2: Date: 2010-10-19, string "Pears on Friday"

In other words, if I select "APPLE", then "2010-10-19", it should return "Apples on Friday".

The following does not work (it selects from the Cartesian Product which is every single combination) of symbol and date):

var daysForSymbolS1 = from Symbol s in db4o.db
                      from Date t in db4o.db
                          where (s.SymbolGlobal == "APPLE" 
                            && t.Date == new DateTime(2010, 10, 18))
                          select new
                          {
                            s.SymbolGlobal,
                            t.Date,
                            t.Meta
                          };

foreach (var d in daysForSymbolS1)
{
  Console.WriteLine("{0} - {1} - {2}", d.SymbolGlobal, d.Date.Date, d.Meta);
} 

I'd love to use a join - but db4o is an object database, and it doesn't let you reference the ID for each object.

Update:

@Gamlor gave a brilliant answer, it works like a charm.

I should also mention that the current version of db4o does not support indexing on collections. Since any form of 1-to-many hierarchy is constructed using something like an IList collection that holds some subclasses, it is likely that breaking the database down into a hierarchy will slow things down, both speed and maintenance wise.

Answer 1

I would expect it to look more like this... but it is hard to tell without seeing the def of db4o.db

var mySym = from Symbol s in db4o.db
            where (s.SymbolGlobal == "APPLE")

var mySomething = from xxx x in mySym.x
                  where (x.Date == new DateTime(2010, 10, 18)
                  select new
                  {
                    mySym.SymbolGlobal,
                    x.Date,
                    x.Meta
                  };

foreach (var d in mySomething)
{
  Console.WriteLine("{0} - {1} - {2}", d.SymbolGlobal, d.Date.Date, d.Meta);
} 

Answer 2

Hogan already answered that you need to use join: For example like this:

       var daysForSymbolS1 = from s in database.Cast<Symbol>()
                                  join t in database.Cast<TradingDay>() on s equals t.SymbolGlobal 
                                  where (s.SymbolGlobal == "IBM" && t.Date == new DateTime(2010, 10, 20))
                                  select new
                                  {
                                      s.SymbolGlobal,
                                      t.Date,
                                      t.Meta
                                  };

Now to the db4o-parts. You just can use the reference itself to do the join. You don't need any ID (as shown above)

However there not the big issue. Currently the LINQ to db4o implementation doesn't support the LINQ-Operator at all (You can actually see that by using Go To Declaration on the join). This means it falls back to LINQ to Objects. That means that all objects are loaded into memory and then the LINQ to Object is executed on those objects. Thats extremely slow.

Currently the query cannot be executed efficiently on db4o. The way you do such stuff usually with db4o is to have a Day-collection on a Symbol. That collection contains always all days of that symbol. Then you can query for the symbol and then get the data.

Another method would be to split the query:

    var ibmSymbols = from Symbol s in database
                     where s.SymbolGlobal == "IBM"
                     select s;

    // run the TradingDay selection with the optimized LINQ to db4o implementation
    // do the rest with LINQ to objects
    var allSymbols = ibmSymbols.SelectMany(s => from TradingDay t in database
                               where t.SymbolGlobal==s && t.Date == new DateTime(2010, 10, 20)
                               select t);

Edit: I just want to add the case when the Symbol has the TradingDays in a list other collection. Then you can do this:

            var ibmSymbols = from Symbol s in database
                             where s.SymbolGlobal == "IBM"
                             select s;

            var tradingDays = from symbol in ibmSymbols
                              from day in symbol.TradingDays
                              where day.Date == new DateTime(2010, 10, 20)
                              select day;