CODEWITHSUNDEEP
sqllinq-to-sql
user984261
user984261

Reputation: 21

How do you convert this SQL statement to LINQ to SQL, with a query in a select statement

The table has staffid, firstname, lastname, email, supervisorid. The supervisorid is linked to the staffid on the same table. 

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

Upvotes: 2

Views: 71

Answers (3)

Jim Wooley
Jim Wooley

Reputation: 10398

Do you really need a subselect, or would a self join suffice here?

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

If you need to do an outer join, use DefaultIfEmpty().

Upvotes: 0

Geoff
Geoff

Reputation: 9340

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

Upvotes: 1

Sorax
Sorax

Reputation: 2203

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

Upvotes: 0

Related Questions