Solved

Linq Distinct Question

Posted on 2012-03-14
2
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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