Solved

Linq Distinct Question

Posted on 2012-03-14
2
224 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

717 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