C# LINQ to SQL - one to many relationship

Question

I have troubles creating this query in LINQ:

USE Northwind
GO

SELECT emp.FirstName, emp.LastName, tr.TerritoryDescription, reg.RegionDescription
FROM Employees emp
INNER JOIN EmployeeTerritories empt ON empt.EmployeeID = emp.EmployeeID
INNER JOIN Territories tr ON tr.TerritoryID = empt.TerritoryID
INNER JOIN Region reg ON reg.RegionID = tr.RegionID

This is my current creation:

var query = await context
                .Employees
                .Select(x => new
                {
                    x.FirstName,
                    x.LastName,
                    TerritoryId = x.EmployeeTerritories. //cannot access properties
                })
                .ToListAsync();

But i can't easily access EmployeeTerritories properties, since it's not 1:1 relationship. I accept both clues and full solution to this problem.

Edit

So this is what i currently have:

            var query = await context
            .Employees
            .Select(x => new
            {
                x.FirstName,
                x.LastName,
                TerritoryDescription = x.EmployeeTerritories
                    .Select(et => et.Territory.TerritoryDescription)
                    .ToList(),
                RegionDesicription = x.EmployeeTerritories
                    .Select(et => et.Territory.Region.RegionDescription)
                    .ToList()
            })
            .ToListAsync();

Is there a way to optimize it? RegionDescription is now a list that contains one element, but i don't know how to do it the better way.

Answer 1

Try something like this (assuming you have corresponding relations):

var query = await context
            .Employees
            .Select(x => new
            {
                x.Employee.FirstName,
                x.Employee.LastName,
                TerritoryDescription = x.EmployeeTerritories
                    .Select(et => et.Territory.TerritoryDescription)
                    .ToList(),
            })
            .ToListAsync();

UPD

To flatten in your particular case you can use solution posted by @dhrumil shah(it is more generic one) or try something like that, if you have EmployeeTerritories set up in your context :

var query = await context
            .EmployeeTerritories
            .Select(et => new
                    {
                        et.Employee.FirstName,
                        et.Employee.LastName,
                        et.Territory.TerritoryDescription,
                        et.Territory.Region.RegionDescription
                    })
            .ToListAsync();

Answer 2

(from emp in context.Employees 

 join empt in context.EmployeeTerritories 
 on emp.EmployeeID equals empt.EmployeeID
 
 join tr in context.EmployeeTerritories 
 on empt.TerritoryID equals tr.EmployeeID 
 
 join reg in context.Region 
 on reg.RegionID equals tr.RegionID 
 
 select new { 
 emp.FirstName, 
 emp.LastName, 
 tr.TerritoryDescription, 
 reg.RegionDescription
 }).ToList();