LINQ: Aggregate Rows based on dictionary or mapping?

Question

I am firing q LINQ query against an SP List to get all Employees involved, how many items belong to them and the overall percentage.

I was now pointed to an error within the data: On several rows 3 users are not listed as "lastname, firstname" but domain\username. I need this inconsistencies in the data flattened out.

One possibility would be to ignore all results where the name contains "domain\". However I'd like to map those 3 usernames to their real names.

   domain\user1 => lastname, firstname
   domain\user2 => lastname, firstname
   domain\user3 => lastname, firstname

and then aggregate the result into the "lastname, firstname" row of this user

My actual code looks like this:

public IEnumerable<EmployeeVM> GetAllEmployees()
    {
        EntityList<TicketsElement> Tickets = _db.GetList<TicketsElement>("Tickets");
        return Tickets.Where(b => b.BearbeiterID != null && b.BearbeiterImnName != String.Empty && b.BearbeiterImnName != null)
                      .GroupBy(b => b.BearbeiterImnName)
                      .OrderByDescending(b => b.Count())
                      .Select(b => new EmployeeVM() { Name = b.Key, val = b.Count() });

    }

What is the best way to map this 3 users and aggregate the corresponding results?

Kind regards

/edit: I have made some slight progress. I have edited the GroupBy to reflect 1 user.

.GroupBy(b => new { b.BearbeiterImnName, BearbeiterName = b.BearbeiterImnName == @"Domain\User1" ? "lastName, firstName" : b.BearbeiterImnName  })

This returns the user twice in the list, which means I just need to find a way to aggregate rows with the same username.

Answer 1

You can try to introduce a conditional statement in your select like this:

.Select(b => new EmployeeVM() { Name = !b.Key.Contains("domain") ? b.key : Your new statement,  val = b.Count() });