Linq for Joining multiple tables

Sasi Reddy

12y2.2kLINQ

How to write query for joining more tables.I have a patient id for all tables as common and i need to retrieve all values what are there in various tables.can you please tell me how to do that?..

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
If you want to return all columns of the joined tables, then you have to list those columns in an anonymous type. There is no way to do this automatically, to my knowledge.

So, if you were joining two tables and table1 had columns called 'name' and 'patientid' and table2 had columns called 'gender', 'age' and 'patientid' your query would be:

var k = from p in context.table1
join p1 in context.table2 on p.patientid equals p1.patientid
select new
{
name = p.name,
patientid = p.patientid,
gender = p1.gender,
age = p1.age
};
Lawrence Pond12y ago
var querySyntax =
from c in Customers
join p in Purchases on c.patientID equals p.patient ID
select new
{
c.Name, p.Description, p.Price
};

var fluentSyntax =
Customers.Join ( // outer collection
Purchases, // inner collection
c => c.patientID, // outer key selector
p => p.patientID, // inner key selector
(c, p) => new // result selector
{
c.Name, p.Description, p.Price
}
);
Pratap Patel12y ago
You can create a common class which holds fields of two or more tables and common fields.

For example, You have two tables as below:
        Table - Department
                                  DeptID(PK),DeptName
        Table - Employee
                                  EmpID(PK),EmpName,DeptId(FK),Salary,......

Then in case you create a class like below

        class EmployeeDetails
        {
                int EmpId,
                String EmpName,
                double Salary,
                String Department        //To store name of department from Department table
        }

Now, You write a LINQ query as below and store result in List of class EmployeeDetails

List empDetails = (from emp in Employee
                                                 join dept in Department
                                                 on emp.DeptId equals dept.DeptId
                                                 select new EmployeeDetails
                                                 {
                                                      EmpId = emp.EmpID,
                                                      EmpName = emp.EmpName,
                                                      Salary = emp.Salary,
                                                      Department = dept.
                                                 }).ToList();

I hope you will got it.
Thank you
Vulpes12y ago
If patientid is a unique column in both tables, then there shouldn't be any duplication.

If I can just demonstrate using a local query:

using System;
using System.Collections.Generic;
using System.Linq;

class Patient
{
public string name { get; set;}
public int patientid { get; set; }
}

class Patient1
{
public int patientid { get; set; }
public char gender { get; set; }
public int age { get; set; }
}

class Test
{
static void Main()
{
Patient wendy = new Patient
{
name = "Wendy",
patientid = 1
};

Patient fred = new Patient
{
name = "Fred",
patientid = 2
};

Patient1 wendy1 = new Patient1
{
patientid = 1,
gender = 'F',
age = 35
};

Patient1 fred1 = new Patient1
{
patientid = 2,
gender = 'M',
age = 45
};

var table1 = new List{wendy, fred};
var table2 = new List{wendy1, fred1};

var k = from p in table1
join p1 in table2 on p.patientid equals p1.patientid
select new
{
name = p.name,
patientid = p.patientid,
gender = p1.gender,
age = p1.age
};

foreach(var p in k)
{
Console.WriteLine("Name = {0}, ID = {1}, Gender = {2}, Age = {3}", p.name, p.patientid, p.gender, p.age);
}

Console.ReadKey();
}
}

The output is:

Name = Wendy, ID = 1, Gender = F, Age = 35
Name = Fred, ID = 2, Gender = M, Age = 45
Sasi Reddy12y ago
Hi Vuples, By using that query i am getting duplicate values ,how to get rid of that problem?..
Abhay Shanker12y ago
you can also check below links

http://www.c-sharpcorner.com/UploadFile/54db21/joins-using-linq-in-C-Sharp/

http://www.mssqltips.com/sqlservertip/3169/understanding-linq-to-join-multiple-tables-where-null-match-are-expected/
Abhay Shanker12y ago
join in Linq

var customers = Customer.SampleData();
                                var orders = Order.SampleData();

                                var q = from c in customers
                                      join o in orders on c.CustomerID equals o.CustomerID into j
                                      from order in j.DefaultIfEmpty()
                                      select new
                                      {
                                         LastName = c.LastName,
                                         //set no order when we have a null value
                                         OrderNumber = order == null ? "(no order)" : order.OrderNumber};
                                      };
Jignesh Trivedi12y ago
hi,
using "join" statement you join two or more tables
like
var k = from p in context.table1
join p1 in context.table2 on p.patientid equals p1.patientid
join p2 in context.table2 on p.patientid equals p2.patientid
select p;
above code is example of inner join.
hope this will help you.