I have 3 tables

Employee, EmployeeAddress, Address

Employee has a pk EmployeeId EmployeeAddress has a coposite key made up of EmployeeId and AddressId And you guessed it: Address has a pk of AddressId (very AdventureWorks-ish )

The problem is that LLBL is detecting the relationship between Employee and Address as m:n via EmployeeAddress when in-fact it is a 1 0/1) relationship (by virtue of EmployeeAddress composite key). I don't want to get back EntityCollection, but a single entity if one exists. I have seen some other post regarding this type of thing in a two table scenario but it is not addressed for three tables. How do I get LLBL to correctly resolve the relationship (unique constraints...)?

May I ask why it is you are doing this? I would say that the Unique Constraint path would be what I would try, but I've never done anything like this where there is a 1 to 1 relationship between both tables to EmployeeAddress.

While, I like to have Address as a separate table/entity. There may also be tables such as Customer/CustomerAddress/Address. Putting Address in a separate entity allows me to build user controls and such that operate on any AddressEntity of who or what it is an address to. Could I have an AddressId field in Employee? Yes, but I like the decoupling the the intermediate table provides. For example at some point I may decide that I do need multiple addresses for an employee? Then I could change the keys add in an AddressType field or something and be good to go. Valid reasoning??? I don't know...perhaps I am just making this harder than it needs to be.

First of all, EmployeeAddress composite key doesn't gaurantee a 1:1 relation at all. It says that this is m:n relation.

While logically speaking this should be 1:m relation (Employee:Address) or as you may wish(something:Address)

So each Address should only be of one thing (a company, a contact, an employee...etc) Design wise, no 2 objects should have the same address, even if they are a married couple of employees (they might separate in the future, so each should have his own Address record)

How to achieve this in the database and using LLBLGen Pro: By having a field in the Address table called SomethingID, and another field called SomethingType.

Then you might use LLBLGen Pro feature of Inheritance to creating a hierarchy of type TargetPerEntityHierarchy.

And having a EmployeeAddress that Inherits from Address, also a ContactAddress that inherits from Address...etc.

And then you can define a relation between EmployeeAddress & Employee Entities ...and so on.

That's the way I've implemented it before.