LINQ to SQL join two tables to select parent table twice based on two different columns from child table
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开发者_如何学Python
- 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
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
精彩评论