开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜