How do I use LINQ group by clause to return unique employee rows?

Question

I'm pretty new to LINQ, and I can't for the life of me figure this out. I've seen lots of posts on how to use the group by in LINQ, but for some reason, I can't get it to work. This is so easy in ADO.NET, but I'm trying to use LINQ. Here's what I have that is relevant to the problem. I have marked the part that doesn't work.

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();

How do I get from the IQueryable to the IList only returning the unique employee rows?

***** Edit: Here is my current output:

[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]

You should use into operator when grouping using linq see i.e. group el by e.EmployeeID into g and then use g is the select e.x https://stackoverflow.com/questions/7325278/group-by-in-linq — Wamiq Rehman, Jan 23 '21 at 23:21
By your data it seems your there is one to many relationship between Location and Employee correct ? — Wamiq Rehman, Jan 26 '21 at 16:03
Yes. There is a one-to-many relationship between Employee and EmployeeLocation. — Bob, Jan 27 '21 at 17:48

Svyatoslav Danyliv · Answer 1 · 2021-01-23T23:49:21.493

1

Actually you do not need grouping here, but Distinct. Ordering before Distinct or grouping is useless. Also AsNoTracking with custom projection is not needed.

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();

edited Jan 23 '21 at 23:49

answered Jan 23 '21 at 23:26

Svyatoslav Danyliv

21,911
3
16
32

I was hopeful that this would work, but it didn't. I realize that Distinct is what I want in SQL. Thanks for the heads up about AsNoTracking(). – Bob Jan 24 '21 at 21:45
This did not work for me: query = query.Distinct().OrderBy(e => e.LastName).ThenBy(e => e.FirstName); It didn't change anything. I added the output for you as well, so you can see. – Bob Jan 24 '21 at 22:49

score 0 · Answer 2 · answered Jan 26 '21 at 16:52

The problem is that few employees have more than one location is causing the results to be repeated.You can handle it in multiple ways. Im using Let clause to tackle the issue in the below example

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();

score 0 · Answer 3 · answered Feb 02 '21 at 16:16

Okay. So here is the solution I came up with. I installed the morelinq NuGet package, which contains a DistinctBy() method. Then I added that method to the last line of the code shown in my problem.

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

How do I use LINQ group by clause to return unique employee rows?

3 Answers3