CODEWITHSUNDEEP
c#linq
gaw
gaw

Reputation: 157

LINQ query translate from SQL which uses subquery containing GROUP BY and COUNT

Firstly, apologies if this has been asked before but I've tried searching through dozens of questions on here (over several days) and I cannot find a suitable one.

I am trying to translate a common SQL query into LINQ. Here is a simplified code version if I was able to use a SQL statement...

List<T> myData = new List<T>(); // (Employee, Age, Department);
myData.Add("Bill", 34, "IT");
myData.Add("Fred", 23, "ACCOUNTS");
myData.Add("Jane", 44, "SALES");
myData.Add("Sally", 56, "IT");
myData.Add("Harry", 33, "ACCOUNTS");


List<T> filteredData = SELECT * FROM myData
                                WHERE Department IN (
                                SELECT Department
                                FROM   myData
                                GROUP BY Department
                                HAVING (COUNT(Department) > 1)
                                )
                        ORDER BY Department, Employee

So that filteredData contains:
                            Fred, 23, ACCOUNTS
                            Harry, 33, ACCOUNTS
                            Bill, 34, IT
                            Sally, 56, IT

This needs to produce a List containing staff within departments that have more than one staff member.

Can anyone translate this requirement into LINQ, both Linq format and using method (extension?) format please?

Upvotes: 2

Views: 263

Answers (2)

nawfal
nawfal

Reputation: 73183

This should work too:

var filteredData = myData.GroupBy(x => x.Department)
                         .Where(x => x.Count() > 1)
                         .SelectMany(x => x)
                         .OrderBy(x => x.Department)
                         .ThenBy(x => x.Employee)
                         .ToList();

Upvotes: 2

VahiD
VahiD

Reputation: 1064

here is the class for abstracting your data: 

public class Emp
        {
            public string Employee { get; set; }
            public string Age { get; set; }
            public string Department { get; set; }
        }

and this is the linq required for doing group by:

private void button1_Click(object sender, EventArgs e)
            {

        List<Emp> myData = new List<Emp>(); // (Employee, Age, Department);
                    myData.Add(new Emp{Employee = "Bill",Age ="34",Department = "IT"});
                    myData.Add(new Emp { Employee = "Fred", Age = "23", Department = "ACCOUNTS" });
                    myData.Add(new Emp { Employee = "Jane", Age = "44", Department = "SALES" });
                    myData.Add(new Emp { Employee = "Sally", Age = "56", Department = "IT" });
                    myData.Add(new Emp { Employee = "Harry", Age = "33", Department = "ACCOUNTS" });

                    var results = from p in myData
                      group p by p.Department into g
                      select new { Department = g.Key, Result = g.Count() };

                    var filteredData = myData.Where(x => results.Where(y => y.Result > 1).Select(z=> z.Department).Contains(x.Department)).OrderBy(em=> em.Department).ThenBy(em2=> em2.Employee).ToList();
    }

For more on how GroupBy works, see Jon Skeet's post on the topic.

Upvotes: 1

Related Questions