CODEWITHSUNDEEP
asp.netasp.net-mvc-3entity-framework-4.1
Joyz
Joyz

Reputation: 31

Entity Framework 4.1 - select Many to Many Association

I use Entity Framwork 4.1 and MVC 3. My question is about Join tables in Entity Framework. I created three tables

  1. User - UserId, Username
  2. Role - Role Id, Rolename
  3. UserInRoles- UserId, RoleId

I pulled these tables to my edmx file, Only two tables appear i.e. the User and Role with a Many to Many Association between two.

If I want to get a user's role from UserInRoles table, What is the approach?

I did something like

var result = (from ar in roles
                          from au in users
                          where au.UserName == username
                          select new {});

This is not working. Please help.

Upvotes: 3

Views: 314

Answers (3)

PCasagrande
PCasagrande

Reputation: 5402

As you guessed, the Entity Framework does combine simple look up tables like that into the parent tables in an effort to help you out. You won't be able to access the look up table in your code, but it will do the heavy lifting and allow you to navigate through the tables like such.

var result = (from ar in roles
    where ar.Users.UserName == username
    select ar);

Or if you prefer lambdas (personal preference):

var result = context.roles.Where(x => x.Users.UserName == username);

Upvotes: 1

Mohnkuchenzentrale
Mohnkuchenzentrale

Reputation: 5885

Entity framework automatically generates a many to many relationship in the model, when there is only a relationshiptable with one shared primarykey that are foreign keys to other tables.

You can either add another column to this table to add it to your model or generate another unique key for the relationship.

Read more about this here : http://learnentityframework.com/LearnEntityFramework/tutorials/many-to-many-relationships-in-the-entity-data-model/

Upvotes: 0

René Wolferink
René Wolferink

Reputation: 3548

They should be linked I suppose:

var result = 
    from au in users
    where au.UserName == username
    select au.Roles;

Not sure if it will give you an empty collection by default. The Roles collection may have to be manually fetched. (Possibly result.Fetch(), but I'm not too familiar with it.)

(Or do you wat access to the actual UserInRole items that are stored in the database?)

Upvotes: 0

Related Questions