如何使用LINQ group by子句返回唯一的员工行?

Question

我对LINQ很陌生，我怎么也弄不明白。我看过很多关于如何在LINQ中使用group by的帖子，但出于某种原因，我不能让它工作。这在ADO中非常容易。. NET，但我试图使用LINQ。这是我手头上与这个问题相关的资料。我已经把不能用的部分做了标记。

public class JoinResult
{
public int LocationID;
public int EmployeeID;
public string LastName;
public string FirstName;
public string Position;
public bool Active;
}
private IQueryable<JoinResult> JoinResultIQueryable;
public IList<JoinResult> JoinResultIList;
JoinResultIQueryable = (
from e in IDDSContext.Employee
join p in IDDSContext.Position on e.PositionID equals p.PositionID
join el in IDDSContext.EmployeeLocation on e.EmployeeID equals el.EmployeeID
where e.PositionID != 1 // Do not display the super administrator's data.
orderby e.LastName, e.FirstName
// ***** Edit: I forgot to add this line of code, which applies a filter
// ***** to the IQueryable. It is this filter (or others like it that I
// ***** have omitted) that causes the query to return multiple rows.
// ***** The EmployeeLocationsList contains multiple LocationIDs, hence
// ***** the duplicates employees that I need to get rid of. 
JoinResultIQueryable = JoinResultIQueryable
.Where(e => EmployeeLocationsList.Contains(e.LocationID);
// *****

// ***** The following line of code is what I want to do, but it doesn't work.
// ***** I just want the above join to bring back unique employees with all the data.
// ***** Select Distinct is way too cumbersome, so I'm using group by.
group el by e.EmployeeID
select new JoinResult
{
LocationID = el.LocationID,
EmployeeID = e.EmployeeID,
LastName = e.LastName,
FirstName = e.FirstName,
Position = p.Position1,
Active = e.Active
})
.AsNoTracking();
JoinResultIList = await JoinResultIQueryable
.ToListAsync();

我如何从IQueryable到illist只返回唯一的员工行?

* * * * *编辑:下面是我当前的输出:

[4][4][Anderson (OH)][Amanda][Dentist][True]
[5][4][Anderson (OH)][Amanda][Dentist][True]
[4][25][Stevens (OH)][Sally][Dental Assistant][True]
[4][30][Becon (OH)][Brenda][Administrative Assistant][False]
[5][30][Becon (OH)][Brenda][Administrative Assistant][False]

Answer 1

实际上这里不需要分组，而是Distinct。在Distinct或分组之前排序是无用的。也不需要AsNoTracking与自定义投影。

var query =
from e in IDDSContext.Employee
join p in IDDSContext.Position on e.PositionID equals p.PositionID
join el in IDDSContext.EmployeeLocation on e.EmployeeID equals el.EmployeeID
where e.PositionID != 1 // Do not display the super administrator's data.
select new JoinResult
{
LocationID = el.LocationID,
EmployeeID = e.EmployeeID,
LastName = e.LastName,
FirstName = e.FirstName,
Position = p.Position1,
Active = e.Active
};
query = query.Distinct().OrderBy(e => e.LastName).ThenBy(e => e.FirstName);
JoinResultIList = await query.ToListAsync();

Answer 2

问题是，很少有员工拥有多个位置，导致结果重复。你可以用多种方法来处理它。在下面的例子中，我使用Let子句来解决这个问题

public class Employee
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int EmployeeID { get; set; }
public int PositionID { get; set; }
}

public class EmployeeLocation
{
public int EmployeeID { get; set; }
public int LocationID { get; set; }
}
public class Position
{
public int PositionID { get; set; }
public string Position1 { get; set; }
}
public class Location
{
public int LocationID { get; set; }
}
public class JoinResult
{
//Suggestion : Insetad of LocationID there should be a varibale that has all the locations of an employee
public IEnumerable<int> LocationIDs;
public int LocationID;
public int EmployeeID;
public string LastName;
public string FirstName;
public string Position;
public bool Active;

}
//Setting up mock data
List<Position> positions = new List<Position>();
positions.Add(new Position() { Position1 = "Dentist", PositionID = 2 });
positions.Add(new Position() { Position1 = "Dental Assistant", PositionID = 3 });
positions.Add(new Position() { Position1 = "Administrative Assistant", PositionID = 4 });
List<Employee> employees = new List<Employee>();
employees.Add(new Employee() { EmployeeID = 4, FirstName = "Amanda", LastName = "Anderson (OH)", PositionID = 2 });
employees.Add(new Employee() { EmployeeID = 25, FirstName = "Sally", LastName = "Stevens (OH)", PositionID = 3 });
employees.Add(new Employee() { EmployeeID = 30, FirstName = "Brenda", LastName = "Becon (OH)", PositionID = 4 });
List<Location> locations = new List<Location>();
locations.Add(new Location() { LocationID = 4 });
locations.Add(new Location() { LocationID = 5 });
List<EmployeeLocation> employeeLocation = new List<EmployeeLocation>();
employeeLocation.Add(new EmployeeLocation() { LocationID = 4, EmployeeID = 4 });
employeeLocation.Add(new EmployeeLocation() { LocationID = 5, EmployeeID = 4 });
employeeLocation.Add(new EmployeeLocation() { LocationID = 4, EmployeeID = 25 });
employeeLocation.Add(new EmployeeLocation() { LocationID = 4, EmployeeID = 30 });
employeeLocation.Add(new EmployeeLocation() { LocationID = 5, EmployeeID = 30 });

var result = (from e in employees
join p in positions on e.PositionID equals p.PositionID
let employeeLocations = (from el in employeeLocation where el.EmployeeID == e.EmployeeID select new { LocationID = el.LocationID })
where e.PositionID != 1 // Do not display the super administrator's data.
orderby e.LastName, e.FirstName
select new JoinResult
{
LocationID = employeeLocations.Select(p=>p.LocationID).First()//Here its just selecting the first location,
LocationIDs = employeeLocations.Select(p=> p.LocationID),//This is my suggestion
EmployeeID = e.EmployeeID,
LastName = e.LastName,
FirstName = e.FirstName,
Position = p.Position1,
}).ToList();

Answer 3

好的。这就是我想出来的解决方案。我安装了morelinq NuGet包，它包含一个DistinctBy()方法。然后，我将该方法添加到问题中所示的代码的最后一行。

JoinResultIList = JoinResultIQueryable
.DistinctBy(jr => jr.EmployeeID)
.ToList();