CODEWITHSUNDEEP
c#sqlsql-serverasp.net-mvc-scaffolding
Suhas
Suhas

Reputation: 83

How to join two tables with one table having distinct value in LINQ sql?

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 ?

Upvotes: 1

Views: 4934

Answers (2)

mm8
mm8

Reputation: 169270

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);
    ...
}

Upvotes: 1

Vijayanath Viswanathan
Vijayanath Viswanathan

Reputation: 8541

(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)

Upvotes: 0

Related Questions