αNerd αNerd - 1 month ago 5
C# Question

Is there a way to show a single employee with each of his projects using a query in Entity Framework

I have two tables

Employees
and
Projects
.

Each employee can have multiple projects. Each project can have multiple employees working on it. So, the connection is many to many.

Foreign key constraints are used in third table
EmployeesProjects
which in Entity Framework is replaced with virtual properties
ICollection<Employee>
in entity
Project
, and
ICollection<Project>
in entity
Employee
.

I am searching for a way to return a selection in which employee data is joined to each of its projects data separately. So, if an employee has 3 projects the result will be 3 rows - each row should have data from the employee and a project of his.

I know how to do it in plain SQL, but i want to do it in base-first manner with Linq. Also, i don't want to use native query from inside Visual Studio.

So far, I have tried:

Select(emp => new { emp, emp.Projects })


but this should be used with
foreach
for
emp.Projects
to give me what I need, while I need to foreach the result.

I played with join but
emp
has no projectId nor
emp.Projects
can expose
employeeId
property (it is obvious but in my desperation I tried with
pr.Employees.FirstOrDefault()
and other ways to expose
EmployeeID
).

I am new to Entity Framework and I wonder if it is possible something that looks so simple at first glance, to be done through the vehicle of LINQ.

Answer

That's a case for .SelectMany():

var tuples = employees.SelectMany(employee => employee.Projects.Select(project =>
    new {employee, project}
));

or implicitly in inline linq:

var tuples = 
    from employee in employees
    from project in employee.Projects
    select new {employee, project};
Comments