如何将此 SQL 语句转换为 LINQ to SQL，并在 select 语句中使用查询

Question

该表包含 staffid、firstname、lastname、email、supervisorid。 supervisorid 链接到同一张桌子上的staffid。

select 
 StaffID,
 FirstName + ' ' + LastName as 'Name',
 Email,
 Supervisor = (Select FirstName + ' ' + LastName
                  From tblStaff staffinner 
            Where staffinner.StaffID = staffouter.SupervisorID)
from tblStaff staffouter

Answer 1

var staff = from s1 in tblStaff
            select new
            {
               StaffID = s1.StaffID,
               Name = s1.FirstName + " " + s1.LastName,
               Email = s1.Email,
               Supervisor = (from s2 in tblStaff
                            where s2.StaffID == s1.SupervisorID
                            select s2.FirstName + " " + s2.LastName).First()
            };

Answer 2

var staff = tblStaff
            .Select(a => new
                         {
                          StaffID = a.StaffID,
                          Name = a.FirstName + " " + a.LastName,
                          Email = a.Email,
                          Supervisor = tblStaff
                                       .Where(b => b.StaffID = a.SupervisorID)
                                       .Select(b => b.FirstName + " " + b.LastName)
                                       .First()
                         };

Answer 3

你真的需要一个子选择，还是一个自连接就足够了？

var staff = from person in tblStaff
            join supervisor in tblStaff on person.StaffID equals supervisor.SupervisorID
            select new 
            {
                person.StaffID,
                Name = person.FirstName + ' ' + person.LastName,
                person.Email,
                Supervisor = supervisor.FirstName + ' ' + supervisor.LastName
            };

如果需要进行外连接，请使用 DefaultIfEmpty()。