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.

I am looking for the best way to actual map and aggregate. I have no idea how to do this as of now — Marco, Apr 05 '13 at 06:41
does user1, user2 and user3 contain the full first and last name? — jordanhill123, Apr 05 '13 at 06:44
Is there any way to change the Sharepoint List to reflect the data and fix the errors so you don't have to work around the problem? — jordanhill123, Apr 05 '13 at 06:46
no, the username contains the lastname and the first letter of the firstname. And I dont have a way of changing and validating the column where the names are located. — Marco, Apr 05 '13 at 06:48

Fabrice Mainguené · Answer 1 · 2013-04-05T08:55:48.003

0

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

edited Apr 05 '13 at 08:55

answered Apr 05 '13 at 08:48

Fabrice Mainguené

486
1
5
18

this would change the name, but would not aggregate the count. – Marco Apr 05 '13 at 09:05
Look this post I think it can help you :http://stackoverflow.com/questions/912188/linq-distinct-by-name-for-populate-a-dropdown-list-with-name-and-value – Fabrice Mainguené Apr 05 '13 at 12:22
I don't need First or Distinct, because it would just take the first result. I need to merge to rows, so there is only one row – Marco Apr 05 '13 at 12:53
look this post too : http://stackoverflow.com/questions/4805826/how-to-write-a-select-count-group-by-sql-query-in-linq – Fabrice Mainguené Apr 05 '13 at 13:41

LINQ: Aggregate Rows based on dictionary or mapping?

1 Answers1