CODEWITHSUNDEEP
c#linq
Thomas
Thomas

Reputation: 34208

LINQ and various joining sample

i just learning LINQ. so first of all i need to be familiar with join with linq. i search google for left outer and right outer join with linq and i got answer like

left outer join

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

right outer join

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

from then code i just could not understand how it is left outer join because no left outer key word is use here. so please tell me how to understand that the join is left outer join or right outer.

when i will use linq then how like operator can be use. 'a%' or '%a' or '%a%'. i saw there is contain method which is bit different.

please discuss the two issue. thanks

Upvotes: 2

Views: 1437

Answers (3)

Magnus
Magnus

Reputation: 46987

Left join Tip, Instead of:

from user in tblUsers
join compTmp1 in tblCompanies
  on user.fkCompanyID equals compTmp1.pkCompanyID into compTmp2
from comp in compTmp2.DefaultIfEmpty()

You can write:

from user in tblUsers
from comp in tblCompanies.Where(c => c.pkCompanyID == user.fkCompanyID).DefaultIfEmpty()

Upvotes: 1

digEmAll
digEmAll

Reputation: 57220

The "join ... in ... on ... into" piece of LINQ query syntax, is translated into a GroupJoin().

GroupJoin() method, for each key in the outer list (or table), returns a list of elements in the inner list (or table) having the same key, or an empty list if such key doesn't exist.

Hence, the left outer join code of your question is clearer:

If JoinedEmpDept (i.e. the list of elements having the same key of the current examined outer list entry) is empty, dept is set to null (thanks to DefaultIfEmpty() method).

Translation in pseudo code:

for each employee in ListOfEmployees  
get the list of dept having ID equal to empl.DeptID   
and set them into JoinedEmpDept  
then for each dept in JoinedEmpDept 
(if empty iterates over a single null dept)  
returns an new element containing:
employee.Name and dept.Name (or null if dept is null)

The right outer join instead, is basically a left outer join with outer and inner lists exchanged.

About the "like" question, you should use string.Contains("a") for '%a%', string.StartsWith("a") for 'a%', string.EndsWith("a") for '%a'

Example:

var query = from el in listOfStrings
            where el.StartsWith("AB")
            select el;

EDIT:

About the IN() operator question...
well, you can use Contains() also for that, or Any():

var inGroup = new []{ "Foo", "Bar" };

var query1 = from el in listOfStrings
             where inGroup.Contains(el)
             select el;
// or equally
var query2 = from el in listOfStrings
             where inGroup.Any(x => el.Equals(x))
             select el;

Upvotes: 2

Matt Ellen
Matt Ellen

Reputation: 11612

The left outer join is so because of this line:

from dept in JoinedEmpDept.DefaultIfEmpty()

which will get all of the employees, even if they are not in a department. The DefaultIfEmpty turns the join into an left outer join, when the SQL is generated.

See this blog post for more details: C#: Left outer joins with LINQ

Upvotes: 1

Related Questions