Huh! What a title... Let me explain... It is somewhat standard practice to have table with multiple columns that are referring to the same entity in the database. For example, CreatedByUserID and ChangedByUserID in some table are both referring to the User table UserID primary key. So, how does this work with LINQ?
Here is the sample DDL used in my examples:
create table Issues
(IssueID int IDENTITY PRIMARY KEY, Description nvarchar(100), ChangedByUserID int, CreatedByUserID int)
create table Users
(UserID int IDENTITY PRIMARY KEY, UserName nvarchar(100))
alter table Issues add CONSTRAINT FK_ChangedBYUserID
FOREIGN KEY (ChangedByUserID) REFERENCES Users(UserID)
alter table Issues add CONSTRAINT FK_CreatedByUserID
FOREIGN KEY (CreatedByUserID) REFERENCES Users(UserID)
If you would to drag the tables from Server Explorer on to the Visual Studio DBML design surface it would look something like picture below. Two columns e.g. CreatedUserID and ChangedUserID both have a foreign key reference to a single User table in the database hence the two arrow lines pointing towards the Issue table.
If you were to peak inside the auto generated file you will find that designer has done its best and created two properties on Issue entity with names of User and User1.
Similarly, it created two properties on User entity with names of Issue and Issue1 respectively.
Well this is pretty much useless when it comes to understanding this model. What is User1 or Issue1?
There are couple of ways handling this problem. First, you could restructure your database and differentiate between user kind and second we could tweak the generated code and make our life easier. Big drawback with a second approach is that you would have to do this every time if you re-generate DBML! Although first approach might be better sometimes its not feasible.
In case that you are not auto-generating the DBML here is how you can make this little bit better.
First remove one of the relationships and than rename the entity to the remaining relationship like so:
The association editor opens up when you double-click the black diamond arrow.
Now this gives us better syntax:
To get the other field (CreatedBy) bound to a table in the DBML designer drop the User table again from the Server Explorer and rename it immediately (even before saving) to the other field entity name. Finally, remove the opposite relationship. Diagram should look something like this:
One more FINAL thing :) Open the properties of the relationship (the arrow) between the last table and expand Child Property. Remove the number from the name (in my case it was Issue1).
Now we get something that makes more sense in this model when trying to write LINQ from both directions...
I am not sure this is really ideal solution but VS should be supporting this kind of scenario out-of-the-box. Let me know if you have found a better way of making this work!
Petar