Solved

Linq Distinct Question

Posted on 2012-03-14
2
221 Views
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:

USERS
ID     NAME     DEPTID
1       John       11
2       Mary      22
3       Jane       11
4       Joan        33

DEPARTMENT
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:

DATASET
DepartmentName     DepartmentID
Marketing                  11
Finance                      22
Warehouse                33
0
Comment
Question by:adskarcox
2 Comments
 
LVL 75

Accepted Solution

by:
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

0
 
LVL 6

Expert Comment

by:Triskelion
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 }
         ).Distinct()
         .ToList().ForEach(dept =>
            Console.WriteLine(dept.DepartmentName.PadRight(15)
               +'\t'+dept.DepartmentId));
      }
   }
}

Open in new window

0

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. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

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