c# - 如何将 SQL 子查询转换为 Linq

Question

我有两张桌子，Employees和Transactions。

第一张表：dbo.Employees

第二张表：dbo.Transactions

我需要将以下查询转换为 lambda 表达式（Linq）：

SELECT Employees.EmployeeId, Trans.LastTrans
FROM dbo.Employees
INNER JOIN 
    (SELECT EmployeeId, MAX(TrasactionDate) LastTrans 
     FROM dbo.Transactions 
     GROUP BY EmployeeId) Trans ON Trans.EmployeeId = Employees.EmployeeId

我想要结果employeeId，LastTrans如下所示：

标签： c#sql-serverentity-frameworklinq

Answer 1

见下文。查询不需要 linq 中的两个 Select ：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Context db = new Context()
            {
                Employees = new List<Employees>() {
                    new Employees() { EmployeeId = 1, Name = "Hossam"},
                    new Employees() { EmployeeId = 2, Name = "Peter"},
                    new Employees() { EmployeeId = 3, Name = "George"}
                },
                Transactions = new List<Transactions>() {
                    new Transactions() { TranactionId = 1, amount = 200, TransactionDate = DateTime.Parse("2020-01-02"), EmployeeId = 1},
                    new Transactions() { TranactionId = 2, amount = 300, TransactionDate = DateTime.Parse("2020-02-02"), EmployeeId = 1},
                    new Transactions() { TranactionId = 3, amount = 1000, TransactionDate = DateTime.Parse("2020-02-25"), EmployeeId = 1},
                    new Transactions() { TranactionId = 4, amount = 5000, TransactionDate = DateTime.Parse("2020-04-01"), EmployeeId = 2},
                    new Transactions() { TranactionId = 5, amount = 7000, TransactionDate = DateTime.Parse("2020-07-16"), EmployeeId = 2},
                    new Transactions() { TranactionId = 6, amount = 8000, TransactionDate = DateTime.Parse("2020-08-06"), EmployeeId = 3},
                    new Transactions() { TranactionId = 6, amount = 600, TransactionDate = DateTime.Parse("2020-09-04"), EmployeeId = 3}
                }
            };
            var results = (from trans in db.Transactions
                           join emp in db.Employees on trans.EmployeeId equals emp.EmployeeId
                           select new { EmployeeId = trans.EmployeeId, LastTrans = trans.TransactionDate }
                           ).GroupBy(x => x.EmployeeId)
                           .Select(x => x.OrderByDescending(y => y.LastTrans))
                           .Select(x => x.First())
                           .OrderBy(x => x.EmployeeId)
                           .ToList();
        }
    }
    public class Context
    {
        public List<Employees> Employees { get; set; }
        public List<Transactions> Transactions { get; set; }
    }
    public class Employees
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
    }
    public class Transactions
    {
        public int TranactionId { get; set; }
        public decimal amount { get; set; }
        public DateTime TransactionDate { get; set; }
        public int EmployeeId { get; set; }
    }
}