首先,如果以前有人问过这个问题,很抱歉,但我尝试在这里搜索了几十个问题(几天),但我找不到合适的问题。
我正在尝试将一个常见的SQL查询转换为LINQ。如果我能够使用 SQL 语句,这是一个简化的代码版本......
List<T> myData = new List<T>(); // (Employee, Age, Department);
myData.Add("Bill", 34, "IT");
myData.Add("Fred", 23, "ACCOUNTS");
myData.Add("Jane", 44, "SALES");
myData.Add("Sally", 56, "IT");
myData.Add("Harry", 33, "ACCOUNTS");
List<T> filteredData = SELECT * FROM myData
WHERE Department IN (
SELECT Department
FROM myData
GROUP BY Department
HAVING (COUNT(Department) > 1)
)
ORDER BY Department, Employee
So that filteredData contains:
Fred, 23, ACCOUNTS
Harry, 33, ACCOUNTS
Bill, 34, IT
Sally, 56, IT
这需要编制一份名单,其中载有拥有一名以上工作人员的部门内的工作人员。
任何人都可以将此要求转换为 LINQ,包括 Linq 格式和使用方法(扩展?)格式吗?
这也应该有效:
var filteredData = myData.GroupBy(x => x.Department)
.Where(x => x.Count() > 1)
.SelectMany(x => x)
.OrderBy(x => x.Department)
.ThenBy(x => x.Employee)
.ToList();
下面是用于抽象数据的类:
public class Emp
{
public string Employee { get; set; }
public string Age { get; set; }
public string Department { get; set; }
}
这是执行分组所需的 LINQ:
private void button1_Click(object sender, EventArgs e)
{
List<Emp> myData = new List<Emp>(); // (Employee, Age, Department);
myData.Add(new Emp{Employee = "Bill",Age ="34",Department = "IT"});
myData.Add(new Emp { Employee = "Fred", Age = "23", Department = "ACCOUNTS" });
myData.Add(new Emp { Employee = "Jane", Age = "44", Department = "SALES" });
myData.Add(new Emp { Employee = "Sally", Age = "56", Department = "IT" });
myData.Add(new Emp { Employee = "Harry", Age = "33", Department = "ACCOUNTS" });
var results = from p in myData
group p by p.Department into g
select new { Department = g.Key, Result = g.Count() };
var filteredData = myData.Where(x => results.Where(y => y.Result > 1).Select(z=> z.Department).Contains(x.Department)).OrderBy(em=> em.Department).ThenBy(em2=> em2.Employee).ToList();
}
有关Groupby如何工作的更多信息,请参阅Jon Skeet关于该主题的文章。