Linq Distinct Question

Posted on 2012-03-14
Last Modified: 2012-03-15
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
Question by:adskarcox
LVL 75

Accepted Solution

käµfm³d   👽 earned 500 total points
ID: 37721239
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


Expert Comment

ID: 37722530
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question