How to avoid hundreds of queries?

I'm sure this is a simple thing, but I've never really worked in much depth with lightspeed.

I have a classic middleman table decomposing a many-many relationship, let's say ProductLanguages where a product can be translated into more than one language and a language applies to more than one product When I'm looking up the products that have a particular language, my query looks like:

List<Product> prods =
            UnitOfWork.ProductLanguages
            .Where(
                pl =>pl.LanguageID == 1234
            )
            .Select(pl => pl.Product)
            .ToList();

What I'm finding is lightspeed is selecting the list of product ids from productlanguages and then issuing a select query for every product id, changing the product id parameter each time. This means for 15 languages, 16 queries will be run

How can I just get lightspeed to do a JOIN in the database?

SELECT p.* FROM pl INNER JOIN p ON pl.ProductID = p.ProductID WHERE pl.LanguageID = 1234

I've been looking at throughassociations and wondering if this is how it's done. At the moment I didn't get any success, I think because my unit of work has gone out of scope and been disposed by the time I come to request mylanguage1234.Products, but there's another, more complex aspect from time to time. For example, there is only one record for english in the ProductLanguages table, and the product ID is null. This means "this language is available on all products". Is such a scenario supportable?

The reason you are seeing an N+1 here is that the product is being fetched for each ProductLanguage after the ProductLanguages have been materialized. We have to interpret this client side as there may be custom logic associated with that property which means it is not specifically just the associated entity you are fetching.

To execute this as a join you can do something like the following:

var query = new Query(typeof(Product));
query.Join = Join.Inner<Product, ProductLanguage>("Id", "ProductId");
query.Expression = Entity.Attribute<ProductLanguage>("LanguageId") == 1234;

var results = unitOfWork.Find<Product>(query);

There is a bit more information about constructing joins using the query syntax in the documentation: http://www.mindscapehq.com/documentation/lightspeed/Advanced-Querying-Techniques/Exploring-the-Query-Object#Toc350168776

In regards to having the ProductId property as nullable, yes that's fine you can mark that end of the association as nullable using the designer. You will need to convert your ThroughAssociation to an explicit one if you are currently using an "auto" through association.

Jeremy