Linq Distinct Question

I'm new to Linq and need a query that I would have used the DISTNCT command for in straight SQL.

Let's say we have these tables:

1       John       11
2       Mary      22
3       Jane       11
4       Joan        33

ID       Name
11       Marketing
22       Finance
33       Warehouse
44       Corporate
55       HumanResources

I need a Linq query that returns the department ID and department Name of all of the departments that are referenced in the USERS table

The returned dataset would look like this:

DepartmentName     DepartmentID
Marketing                  11
Finance                      22
Warehouse                33
Who is Participating?
käµfm³d 👽Commented:
You might try:

var query = from u in db.Users
            join d in db.Departments
            on u.DeptId equals d.ID
            group new { Name = d.DepartmentName, ID = d.DepartmentID } by u.DeptID into g
            select g;
foreach (var dept in query)
    Console.WriteLine("{0}: {1}", dept.Name, dept.ID)

Open in new window

You could still use Distinct, but as @kaufmed shows, it's not necessary.
Here is a mock-up in C#
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_27632122_CS_CON
   public class CUser
      public int userId { get; set; }
      public string name { get; set; }
      public int deptId { get; set; }

   public class CDepartment
      public int deptId { get; set; }
      public string deptName { get; set; }

   public class CDb
      public List<CUser> Users { get; set; }
      public List<CDepartment> Departments { get; set; }

   class Program
      static void Main(string[] args)
         CDb db = new CDb();
         db.Users = new List<CUser>()
            new CUser() {userId=1, deptId=11, name="John"},
            new CUser() {userId=2, deptId=22, name="Mary"},
            new CUser() {userId=3, deptId=11, name="Jane"},
            new CUser() {userId=4, deptId=33, name="Joan"}

         db.Departments = new List<CDepartment>()
            new CDepartment(){deptId=11, deptName="Marketing"},
            new CDepartment(){deptId=22, deptName="Finance"},
            new CDepartment(){deptId=33, deptName="Warehouse"},
            new CDepartment(){deptId=44, deptName="Corporate"},
            new CDepartment(){deptId=55, deptName="HumanRacehorses"},

            from user in db.Users
            join dept in db.Departments on user.deptId equals dept.deptId
            select new { DepartmentName = dept.deptName, DepartmentId = dept.deptId }
         .ToList().ForEach(dept =>

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.