How to get many-to-many relationships in ADO.Net entity from a MSSQL database

I was fricking long searching for a simple guide to get ado.net entity model with many-to-many relationships.

Here is the solution.

We have  “User” and “Account” table. A user can have many accounts and accounts can be owned by multiple users. To implement this many-to-many relationship you’ll need to create a seperate table with 2 fields.

Let’s say we create a UserAccount table, with 2 fields: user_id, account_id.  First step is to create the two relationships. A relationship between “user_id” and the “User” primary key. And the same for “Account” and “account_id”. That’s logical stuff I would say.

We now have a join table, but if we try to generate the ADO.Net entity stuff in visual studio, we get the table itself, not the direct relationship. Or it moans about not having a primary key (which is true).

So, to get the relationship we need to create a primary key, not just a primary key but a composite key.
You can create one easily in the database designer. Just Select the two fields and rightclick and select “Set Primary Key”. You should now get two yellow key symbols, one in front of both fields.

If you now generate the Ado.Net Entity thingy, you get the relationship properly, without having to use User.AccountUser.Account. It’s now User.Account.

Maybe I am blind, but I couldn’t find this anywhere…. 😐

Back to work. 🙂

Update: Don’t forget to use User.Account.Load() before accessing User.Account.ToList() or similar. The framework doesn’t autoload children, but you need to explicitly load them. (Quite logical if you think about it).