╬▒Nerd ╬▒Nerd - 4 months ago 29
C# Question

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

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 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 and i wonder if it is possible something that looks so simple at first glance, to be done through the vehicle of LINQ.


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};