LINQ to SQL join two tables to select parent table twice based on two different columns from child table

Question

I'd like to get suggestion in both C# and VB.NET

I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.

Table structures:

• Employees: EmployeeId(PK) , FirstName, LastName
• CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table

Table relationship:

• Employees 1:N CafeLogs (CashierId (FK))

Employees table data:

EmployeeId, FirstName, LastName

• 1 , Steve , Marks
• 2 , Louis , John
• 3 , Scoot , Joe
• 4 , Steven, Gerald

• 5, Brown , Lexus

  CafeLogs table data:

  LogId, CashierId, EmployeeId, Value

  • 1 , 2 , 3 , 9
  • 2 , 3 , 4 , 12
  • 3 , 2 , 5 , 8
  • 4 , 3 , 1 , 4
  • 5 , 3 , 1 , 2
  • 6 , 2 , 5 , 9
  • 7 , 2 , 4 , 1

Wanted Result :I want to show both Cashier name and Employee name like this from one query:

• LogId, Employee, Charged by, Value
• 1 , Scoot Joe, Louis John, 9
• 2 , Steven Gerald, Scoot Joe, 12
• 3 , Brown Lexus, Louis John, 8
• 4 , Steve Marks, Scoot Joe, 4
• 5 , Steve Marks, Scoot Joe, 2
• 6 , Brown Lexus, Louis John, 9

• 7 , Steven Gerald, Lours John, 1

  Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.

   Dim query = From log In db.CafeLogs _
              Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _
              Select log.LogId, emp.FirsName, emp.LastName, log.Value
  

Answer 1

This should do it in C#:

var query = from log in db.CafeLogs
            join emp in db.Employees on log.EmployeeId equals emp.EmployeeId
            join cas in db.Employees on log.CashierId  equals cas.EmployeeId
            select new
            {
                log.LogId,
                Employee  = emp.FirstName + " " + emp.LastName,
                ChargedBy = cas.FirstName + " " + cas.LastName,
                log.Value
            };

and the VB version:

Dim query = From log In db.CafeLogs _
            Join emp In db.Employees On log.EmployeeId Equals emp.EmployeeId _
            Join cas In db.Employees On log.CashierId  Equals cas.EmployeeId _
            Select _
                log.LogId, _
                Employee  = emp.FirstName & " " & emp.LastName, _
                ChargedBy = cas.FirstName & " " & cas.LastName, _
                log.Value