I have a M:n relationship between 2 entities (Company and Address) resolved through CompanyAddress. I need a list of companies and the city and state for their "Primary" address if it exists, otherwise, I still need the company info.

In sql, it would look like:

Select *
  From Company
  Left Outer Join CompanyAddress CA on CA.CompanyID = Company.CompanyID
 Inner Join Address on Address.AddressID = CA.AddressID and Address.Type = "PRIMARY"
 Where Company.Name Like 'ABC%'

Any ideas how to achieve both the outer join and the Address.Type = "PRIMARY" as a join criteria?

Thanks for your help. --jw

When adding a relation to a RelationCollection you can select an overload of Add() which accepts a JoinHint. This joinhint is between teh startentity and end entity of the relation.

To add additional filters to a join's ON clause, use the CustomFilter property of a relation. Please see: "Using the generated code / Adapter/selfservicing / Filtering and Sorting -> Custom filters for EntityRelations"

Thanks. My mistake was flagging the custom filter as "CustomFilterReplacesOnClause" True, which was yielding a cartesian product. I suppose I misread the docs, but now that I re-read, it couldn't be more clear.

Working perfectly now.

I've been evaluating for 3 days now. Great product.

Thanks.

jw wrote:

Thanks. My mistake was flagging the custom filter as "CustomFilterReplacesOnClause" True, which was yielding a cartesian product. I suppose I misread the docs, but now that I re-read, it couldn't be more clear.

Working perfectly now.

Glad it's solved!

I've been evaluating for 3 days now. Great product.

Thank you!