Retrieve data from many to many relationship in asp.net mvc linq

Question

I have problem retrieving data in many to many relationship using linq.

I have 3 tables

Employee
role
RoleInemployee

The third table has two columns: empid and roleid

An employee can assigned to multiple roles are stored in third table or you can say single role assigned to multiple employee.

I just want to retrieve employee who have particular role id using linq.

I'm using navigation property in employee model like

public virtual ICollection<Role> role {get;set;}

This navigation property give me role for particular employee but I want to retrieve employees data which is having a particular role is assigned.

Can any body help?

see edited answer... for edited question.

Raphaël Althaus
– Raphaël Althaus

2013-01-23 23:03:23 +00:00
Commented Jan 23, 2013 at 23:03 — Raphaël Althaus
– Raphaël Althaus, Commented Jan 23, 2013 at 23:03

Raphaël Althaus · Accepted Answer · 2013-01-23 23:02:15Z

2

Count has an overload with a predicate as parameter, so you can do

   var count =  db.Employees.Count(emp=> emp.role.Any(r=> r.Id == 12));

Edit

to get employees with a particular role

var employees = db.Employees.Where(emp => emp.role.Any(r => r.Id == 12));

edited Jan 23, 2013 at 23:02

answered Jan 23, 2013 at 16:41

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

areich · Accepted Answer · 2013-01-24 18:25:58Z

1

If I understood the question correctly you want to get the number of employees set to a particular role id using LINQ.

If your entity framework Employee class has a list of roles included -- you can do the following with your list of employees where 123 is the RoleID you are looking for:

employeeList.Where(x => x.Roles.Where(y => y.RoleID == 123)).Count();

EDIT: Removed RoleInEmployee code

edited Jan 24, 2013 at 18:25

answered Jan 23, 2013 at 16:40

areich

1166 bronze badges

2 Comments

Raphaël Althaus Over a year ago

For second solution : in EF, RoleInEmployee class probably doesn't exist (this is an object world, not a db one, and it's not needed in object).

hungry Over a year ago

yes RoleInEmployee class does not exist.can we load data on employee object for particular role.can you tell me how to made linq query for that

Collectives™ on Stack Overflow

Retrieve data from many to many relationship in asp.net mvc linq

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related