1

I have two tables emp_details where i have emp_id, emp_name and emp_addresss as columns and another table emp_hierarcy where i have emp_id, emp_mgid with multiple rows with same emp_id.

I want to write a linq query i.e, to join two table upon emp_id with distinct emp_id in emp_hierarcy. I know how to join tables in sqlserver and i have return this query in sqlserver 

SELECT
    DISTINCT
    eh.emp_id
FROM
    emp_details ed
    LEFT OUTER JOIN emp_hierarcy eh ON ed.emp_id = eh.emp_id

i'm able to print only emp_id how to get all the details in LINQ query ?

Suhas
  • 83
  • 2
  • 17
  • Take a look at this: https://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda. To get the distinct values, the `enumerable` should have a `Distinct` method. – Xedni Aug 31 '17 at 16:39
  • Are you using Entity Framework? You generally don't need to perform JOINs in Linq-to-Entities if you use Navigation Properties. – Dai Aug 31 '17 at 16:41
  • Look at GROUP BY and how to do it in Linq. – Vojtěch Dohnal Aug 31 '17 at 16:44
  • @Dai how is it possible actually can you please explain me with an example im new to MVC – Suhas Aug 31 '17 at 16:44

2 Answers2

1

Select all fields that you are interested in:

var items = (from a in emp_details
           join b in emp_hierarcy on a.emp_id equals b.emp_id
           select new 
           {
               emp_id = a.emp_id,
               emp_name = a.emp_name,
               emp_address = a.emp_address,
               emp_mgid = b.emp_mgid
           }).Distinct();

foreach(var item in items)
{
    Console.WriteLine(item.emp_address);
    ...
}
mm8
  • 163,881
  • 10
  • 57
  • 88
0
(from a in emp_details
               join b in emp_hierarcy on a.emp_id equals b.emp_id
               select new 
               {
               emp_id = a.emp_id
               }).Distinct();

Alternatively you can try,

(from a in emp_details
               join b in emp_hierarcy on a.emp_id equals b.emp_id
               select new 
               {
               emp_id = a.emp_id
               }).DistinctBy(c => c.emp_id)
Vijayanath Viswanathan
  • 8,027
  • 3
  • 25
  • 43