LINQ - Join - Asked By goldy gupta on 04-Jan-12 05:14 AM

Helo to all can anybody tell me by taking simple example with two tables
that how i make join in Linq i.e all the join  inner join, left, right, e.t.c....
Riley K replied to goldy gupta on 04-Jan-12 05:17 AM


This is a simple join using LINQ which returns the results to a list

List<video> uvids = (
  from c in db.video
  join o in db.user_videos
  on c.vid equals o.vid
  where o.uid == 2
  select c
).ToList();


Regards
smr replied to goldy gupta on 04-Jan-12 05:18 AM
hi

Let’s compare that to the equivalent query using the join syntax, using the mysteriously popular DefaultIfEmpty trick:



refer
http://blogs.teamb.com/craigstuntz/2010/01/13/38525/

kalpana aparnathi replied to goldy gupta on 04-Jan-12 05:19 AM

How to JOIN two tables using LINQ to SQL

Posted on 02/11/2025 by Eric Fickes

Wanted to share this since it gave me so much trouble figuring out.  It’s a simple SQL query ported to LINQ to SQL that joins two tables to return a filtered listed of data.

Here are the tables from my schema
user, user_video, video tables

Here is a basic SQL statement I could fire to retrieve my user videos.

?
1
2
3
4
select  *
from    video v, user_videos uv
where   v.vid = uv.vid
and   uv.uid = 2

User 2 has two videos
Here is how you would run the same query using .net’s LINQ to SQL.

?
01
02
03
04
05
06
07
08
09
10
// create DB connection
var db = new DBCONN();
// run query
List<video> uvids = (
  from c in db.video
  join o in db.user_videos
  on c.vid equals o.vid
  where o.uid == 2
  select c
).ToList();

This query differs slightly from the screenshot below because I used it in a WCF Service.

Same data, different retrieval method

The variable DBCONN is my database connection that I established when mapping my DB.  If you are not familiar with how to set this up, use the Visual Studio’s “Add the ADO.NET Entity Data Model” wizard.  With your .net project open, right click your project, left click on “Add the ADO.NET Entity Data Model”.  This wizard will walk you through setting up everything you need to setup your DB model file ( edmx ), as well as setting up your database connection and saving it in web.config.

Chintan Vaghela replied to goldy gupta on 04-Jan-12 06:03 AM

Hello,

 

Using LINQ, Inner Join

 

 

var InnerJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID 
select new               
{
EmployeeName = emp.Name,
DepartmentName = dept.Name
};

 

 

 

 Left Join can be acheived as follows

 

var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept 
from dept in JoinedEmpDept.DefaultIfEmpty()
select new               
{
EmployeeName = emp.Name,
DepartmentName = dept != null ? dept.Name : null               
};

 

And for Right Join there is no pretty difference, we just need to reverse the joining in first 2 lines. Here it follows

var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new               
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};

 

 

Hope this is helpful !

Thanks

 

 

 

 

 

[)ia6l0 iii replied to goldy gupta on 04-Jan-12 12:43 PM
There are much better examples than the ones posted above and all are at one place - MSDN Code samples. 

Here is a link for LINQ Join Operators - There are quite a number of examples.
http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9