Linq for Joining multiple tables

Question

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?..

Vulpes · 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 };

Jignesh Trivedi · Answer

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.

Lawrence Pond · Answer

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 Patel · Answer

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

Vulpes · Answer

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 Reddy · Answer

Hi Vuples, By using that query i am getting duplicate values ,how to get rid of that problem?..

Abhay Shanker · Answer

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 Shanker · Answer

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