CODEWITHSUNDEEP
c#.netlinqlambda
csharp newbie
csharp newbie

Reputation: 11

Group by and count on list lambda c#

I have 2 tables in the database Department and SubDepartment. Each department can or cannot have multiple sub departments. I am fetching the data from both the tables combined in the form of following class.

This class have more properties as well from both the tables.

public class Department
{
   public int DepartmentId { get; set; } 
   public string DepartmentName { get; set; } 
   public int SubDepartmentId { get; set; } 
   public string SubDepartmentName { get; set; } 
}

For example data can be Department as Science which will have multiple Sub Departments as Physics, Chemistry. Also Department can be English which will have one sub department as English only.

Now from the data in the form of List of Departments collection fetched from the DB I have to fetch those departments which have multiple sub departments.

Below is the code:

List<Department> departments = new List<Department>();
// Here fetching deptids which are having multi sub deptids
var multidepartmentIds = departments .GroupBy(x => x.DepartmentId)
           .Where(x => x.Count() > 1)
           .Select(x => new { DepartmentId = x.Key }).ToList();

// Here getting the entire data/item for each dept and subdept
var finalDeptData = departments.Where(x => multidepartmentIds.Any(y => y.DepartmentId == x.DepartmentId)).ToList();

Is there a better way to implement this?

I have thought of one more way as well where in a single lambda statement after Count we can have an anonymous type with all the class properties but the problem is that I have to return this collection to the caller method and then in that case I have to return the anonymous collection as type object which will require changes in the caller method as well.

Any help??

Upvotes: 1

Views: 851

Answers (2)

Mehrdad Dowlatabadi
Mehrdad Dowlatabadi

Reputation: 1335

Looking at your code, IMO you are trying to get a list of all sub-departments which their department has multiple sub-department. In this case, you can do this:

List<Department> departments = new List<Department>();
// Here fetching deptids which are having multi sub deptids
var multidepartmentIds = departments
       .GroupBy(x => x.DepartmentId)
       .Where(x => x.Count() > 1)
       .SelectMany(x => x)
       .ToList();

Upvotes: 1

NetMage
NetMage

Reputation: 26917

There is no need to create an anonymous type to hold a single value. Since you are looking for matches in the collection of IDs, use a HashSet to hold them:

// Here fetching deptids which are having multi sub deptids
var multidepartmentIds = departments.GroupBy(d => d.DepartmentId)
                                    .Where(dg => dg.Count() > 1)
                                    .Select(dg => dg.Key)
                                    .ToHashSet();

// Here getting the entire data/item for each dept and subdept
var finalDeptData = departments.Where(d => multidepartmentIds.Contains(d.DepartmentId)).ToList();

However, since all you really want is all the departments where there are multiple occurrences, you can do that in one statement:

// find all departments with multiple sub deptids and return them
var finalDeptData2 = departments.GroupBy(d => d.DepartmentId)
                                .Where(dg => dg.Count() > 1)
                                .SelectMany(dg => dg)
                                .ToList();

I like to have an extension method for this:

public static class IEnumerableExt {
    public static IEnumerable<T> DuplicatesBy<T,TKey>(this IEnumerable<T> src, Func<T,TKey> keyFn, IEqualityComparer<TKey> comparer = null)
        => src.GroupBy(s => keyFn(s), comparer).Where(sg => sg.Count() > 1).SelectMany(sg => sg);
}

Which you can use like:

var findDeptData3 = departments.DuplicatesBy(d => d.DepartmentId).ToList();

Upvotes: 0

Related Questions