Getting employees in each dept using LINQ LAMBDA

Question

I am trying to join two tables employee and department using LINQ and trying to get results in given below image format. Can some one help how to achieve this.

how to achieve employees present in each department.

Here is my code

var EmpList = (from d in Department
 join e in Employee on d.ID equals e.ID
 select new
 {
   ID = d.ID, Name = d.Name, Location = d.location,  Employess =  
   e.FirstName, e.LastName, e.Gender
 });

The above code not fully written. I am not getting any ideas how to achieve this.

var elist = from d in db.Departments
                    join e in db.Employees on d.ID equals e.ID
                    group d by e.DepartmentId into g
                    select new { Details = g };

Change your join's on condition to d.ID equals e.DepartmentID and write groupby d.Name — Samvel Petrosov, Apr 26 '17 at 03:15
Perhaps this article will help? http://stackoverflow.com/questions/7325278/group-by-in-linq — ReRoute, Apr 26 '17 at 03:20

Yeldar Kurmangaliyev · Accepted Answer · 2017-04-26T03:44:51.790

Assuming that you have a structure like this:

var depts = new[] {
    new Dept { ID = 1, Name = "IT", Location = "New York" },
    new Dept { ID = 2, Name = "HR", Location = "London" },
    new Dept { ID = 3, Name = "Payroll", Location = "Sydney" }
};

var employees = new[] {
    new Employee { ID = 1, FirstName = "Mark", DeptID = 1 },
    new Employee { ID = 2, FirstName = "Steve", DeptID = 3 },
    new Employee { ID = 3, FirstName = "Ben", DeptID = 1 },
    new Employee { ID = 4, FirstName = "Philip", DeptID = 2 },
    new Employee { ID = 5, FirstName = "Mary", DeptID = 2 },
    new Employee { ID = 6, FirstName = "Valarie", DeptID = 3 },
    new Employee { ID = 7, FirstName = "John", DeptID = 1 }
};

You can use LINQ Join and GroupBy to get the desired data:

var result = depts
    .Join(employees.GroupBy(x => x.DeptID), dept => dept.ID, empGroup => empGroup.Key,
        (dept, empGroup) => new { 
            Name = dept.Name, 
            Location = dept.Location, 
            Employees = empGroup.ToArray() 
        });

Or the same in SQL-like syntax:

var result = from dept in depts
             join empGroup in (
                from e in employees
                group e by e.DeptID into g
                select g
             ) on dept.ID equals empGroup.Key
             select new { 
                Name = dept.Name, 
                Location = dept.Location, 
                Employees = empGroup.ToArray()
             };

What does this actually do?

Employees are grouped by DeptID.
Departments are joined with this grouping and result into a collection of anonymous objects of desired format. Of course, you can use a strongly-typed class.

@IvanStoev Wow, yeah :D Thank you, I did never hear about this method before. — Yeldar Kurmangaliyev, Apr 26 '17 at 08:16
Ivanstoev your solution is working great ..I tried in different way but its not working can let me know what i did wrong. Just want to know my mistake ..i copied my code in question — Tronics, Apr 26 '17 at 12:51

score 0 · Answer 2 · answered Oct 29 '18 at 10:30

0

public class Meta
{
}

public partial class Empolyees

{
    public string depart
    {
        get
        {
            EmpolyeeEntities db = new EmpolyeeEntities();

            var empid = db.Empolyees.Select(e=>e.Department).SingleOrDefault();

             var dpname = db.Department1.Where(x => x.Id == empid).Select(f => f.Department).SingleOrDefault();


            return dpname.ToString();
        }
    }

answered Oct 29 '18 at 10:30

srikar

1

empid get the ids of employee table ,by using it bring rows of department table using where then using select() get the valu of department – srikar Oct 29 '18 at 10:33

Getting employees in each dept using LINQ LAMBDA

2 Answers2

Linked