I have two tables in an Oracle database, Contact and Product. These two tables are connected through a Producer table, which has a 1:1 relation with Product. There must be and can only be one Producer for one Product.

I want to fetch a list of products which are produced by a Contact. My first quess would be:

var query = metadata.Product.Where(p=>p.Producer.ContactID == 1);

This results in an ORMQueryConstructionException:

The property 'Producer' isn't mapped to a field or database construct of entity type 'ProductEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ?

When I was dealing with a 1:n relation, I would use the following code to fetch the list:

var producers = metadata.Producer.Where(p=>p.ContactID == 1).Select(p=>p.ProductID);
var query = metadata.Product.Where(p=>producers.Contains(p.ProductID);

I was expecting the first linq query provide the same SQL statement, with an EXISTS, as the second linq query. Or am I wrong?

I am using LLBLGen Pro 3.1, version 3.1.12.0507

Does the product entity have a Producer navigator? That's the cause of the problem in your first query: it looks like it doesn't. (as in: the 'Producer' property is a custom property added to the type, not a navigator) ?

it is a custom property, added in a partial class...

Try using the actual navigator, as the expression tree is constructed based on that info, or use your old way.