Fluent Query Multiple Join

[matt-gen]

I'm trying to do a multiple join to a second column that is referenced from the first join. So in effect find out which stores the product is in.

var q = new MyDB().SelectColumns(Store.NameColumn)
.From<Product>()
.InnerJoin<Shelf>(Product.ShelfIdColumn, Shelf.IdColumn)
.InnerJoin<Store>(Shelf.StoreIdColumn, Store.IdColumn)
.Where(Product.IdColumn).IsEqualTo(5);

The problem is that SubSonic is trying to join the Store table directly to the Product table. I see in the source the comment:

//the assumption here is that the FromTable[0] is the table to join from

So every join is going to be attempted on the Product table. 2.1 had the option of passing in qualified names for the join. Is there something I'm missing as a way to do this?

[rally25rs]

The syntax isn't as pretty, but this Linq creates a proper SQL statement:

          var joined = db.Product
            .Join(db.Shelf, a => a.ShelfIdColumn, b => b.IdColumn, (a, b) => new { a = a, b = b })
            .Join(db.Store, x => x.b.StoreIdColumn, c => c.IdColumn, (x, c) => new { x = x, c = c })
            .Where(x => x.x.a.IdColumn == 5)
            .Select(x => x.c.NameColumn);

I'll have to assume the bug lies within that fluent InnerJoin method.

[matt-gen]

Thanks for taking the time to come up with a workaround. I ended up finding a few other spots where the fluent query system was not as robust as in 2.1, and so as not to have to redo significant parts of my repository layer, I've gone back to 2.1. Although I enjoyed the T4s so much that I modified them to work for 2.1.

I won't close the issue as I believe it is still relevant, however I won't be trying to fix it.

[Earlz]

@Tho77, I'm looking for alternatives to Subsonic 3 due to performance issues. Are the T4 templates you created open source and hosted somewhere?