Solved

Linq Distinct Question

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I need help embedding Base64 Images using VB.Net - When image is a .jpg 2 42
xpath sql query 2008 8 44
SQL Login 17 40
Javascript and Jquery not firing 9 42
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

861 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now