LINQ Select within a Select

Question

I am trying to query a collection that contains Employee information. When I query that collection I would like to return an enumeration of objects where each object has two fields:

• Name
• ManagerName

(Note that every Manager is also an Employee!)

Now, here's the problem I am having. When I do a select within a select, the value of the ManagerName field that is returned on each object is:

System.Data.Common.Internal.Materialization.CompensatingCollection<string>

Here's the query:

var query =
    from e in db.Employees    
    select new
    {
        Name = e.Name,
        ManagerName =
            from em2 in db.Employees
            where (em2.EmployeeID == e.ManagerID)
            select em2.Name
    };

Specifically, when I look at the value of ManagerName, I see that it is an enumeration that yields a single item. And that the single item is a string that contains the name of the Manager. So, I think I'm close.

Question: How can I change my query so that instead it returns an enumeration of objects where each object simply has two string fields, Name and ManagerName?

Answer 1

Try this:

var query = from e in db.Employees
            select new
            {
                Name = e.Name,
                ManagerName = db.Employees
                                .Where(x => x.EmployeeID == e.ManagerID)
                                .Select(x => x.Name).SingleOrDefault()
            };

However, if you correctly mapped your database with EF (which I suppose you are using), you should have a navigation property you can utilize:

var query = from e in db.Employees
            select new
            {
                Name = e.Name,
                ManagerName = e.Manager.Name
            };

Answer 2

Looks like a self-join should work:

var query = from e in db.Employees
            join m in db.Employees on e.ManagerID equals m.EmployeeID
              select new
              {
                Name = e.Name,
                ManagerName = m.Name
              };