Linq supports joining just like any other querying language. Syntax is bit different from standard SQL query though. Also We need to add some logic to achieve Left Outer Join as there is no standard keyword for this.
In our example, we have list of people with their name and their address. We also have list of houses in city with their house name and owner name.
a. Lets understand performing inner join. Inner join joins two tables based on some condition. Results will include only those entries from joined tables which match the condition. To illustrate inner join we need to find out all the people who own a house with their address. We use "join" keyword with "on" clause to achieve this inner join. Example I have provided is self explanatory.
b. If we have to output all the people along with their house names if they own otherwise print empty string, then we can go for left outer join. In this join lets make People as left item. We use DefaultIfEmpty() method to ensure for every People entry will appear in output result. DefaultIfEmpty() provides empty\default for each non matching sequence i.e for any People object if matching sequence in Houses is empty then DefaultIfEmpty returns single default item. In next select we can check if result is default or contains some value. If result sequence contains some value other than default, then we can output that value otherwise we can output empty string or default value.
Below example shows how Left Outer Join and Inner Joins are performed in our example:
In our example, we have list of people with their name and their address. We also have list of houses in city with their house name and owner name.
a. Lets understand performing inner join. Inner join joins two tables based on some condition. Results will include only those entries from joined tables which match the condition. To illustrate inner join we need to find out all the people who own a house with their address. We use "join" keyword with "on" clause to achieve this inner join. Example I have provided is self explanatory.
b. If we have to output all the people along with their house names if they own otherwise print empty string, then we can go for left outer join. In this join lets make People as left item. We use DefaultIfEmpty() method to ensure for every People entry will appear in output result. DefaultIfEmpty() provides empty\default for each non matching sequence i.e for any People object if matching sequence in Houses is empty then DefaultIfEmpty returns single default item. In next select we can check if result is default or contains some value. If result sequence contains some value other than default, then we can output that value otherwise we can output empty string or default value.
Below example shows how Left Outer Join and Inner Joins are performed in our example:
public static void Main(string[] args)
{
People p1 = new People { Name = "Manu", Address = "Marathalli"
};
People p2 = new People { Name = "Dada", Address = "BTM" };
People p3 = new People { Name = "Saavu",
Address = "Kunadanahalli"
};
People p4 = new People { Name = "Vasanth",
Address = "Munnekolala"
};
People p5 = new People { Name = "Andy", Address = "Jayanagar" };
People p6 = new People { Name = "Putta",
Address = "JP Nagar" };
Houses h1 = new Houses { HouseName
= "Jaipur
Palace", HouseOwnerName = "Putta" };
Houses h2 = new Houses { HouseName
= "Mysore Palace", HouseOwnerName = "Manu"
};
Houses h3 = new Houses { HouseName
= "Pune Vihar", HouseOwnerName
= "Vasanth"
};
List<People>
people= new List<People> { p1, p2, p3,
p4, p5, p6 };
List<Houses>
houses = new List<Houses> { h1, h2, h3
};
Console.WriteLine("Inner Join Results:");
//Inner join to print all the
Person with house with house name
var
houseOwners = from p in people
join h in houses on p.Name equals h.HouseOwnerName
select new { p.Name, PersonHouseName = h.HouseName, p.Address };
foreach
(var h in houseOwners)
{
Console.WriteLine(h.Name+"
: "+h.PersonHouseName+" : "+h.Address);
}
Console.WriteLine("************************************************************************");
//Left outer join to print all the people and also their
house names if they have one otherwise empty string
Console.WriteLine("Left Outer Join
Results:");
var
allppl = from p in people
join h in houses on p.Name equals h.HouseOwnerName into tempOwners
from nonOwners in tempOwners.DefaultIfEmpty()
select new { p.Name, PersonHouseName = (nonOwners==null)? string.Empty:nonOwners.HouseName,
p.Address };
foreach
(var h in allppl)
{
Console.WriteLine(h.Name + "
: " + h.PersonHouseName + " : " + h.Address);
}
}
public class People
{
public string Name {set;get;}
public string Address { set;
get; }
}
public class Houses
{
public string HouseName { set; get; }
public string HouseOwnerName { set; get; }
}
Console Output Window:
