• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
1 Solution
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


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now