In SQL, we can joins tables using inner and left joins as follows:
Inner Join: the following query returns only the products that have a valid category id.
Select prod.id, prod.name, cat.name
From product prod
Inner join category cat on prod.catid = cat.catid
Left Outer Join: the following query returns all the products even if there is no valid category id
Select prod.id, prod.name, cat.name
From product prod
Left outer join category cat on prod.catid = cat.catid
If you are using the Entity framework and you are trying to join entities in your code, you can rewrite the queries using entity SQL as follows:
Inner Join:
This looks basically the same as a normal SQL inner join. The join is between product and category on the catid field. If no category exists, no data would return.
This looks basically the same as a normal SQL inner join. The join is between product and category on the catid field. If no category exists, no data would return.
var list = from proc in en.Product
join cat in en.Category on proc.catid equals cat.catid
select new{
id = prod.id,
name=prod.name,
category=cat.name
}
Left Outer Join:
The following example does a group join between product and category. This is essentially the left join. The into expression returns data even if the category table is empty. To access the properties of the category table, we must now select from the enumerable result by adding the from cl in catList.DefaultIfEmpty() statement.
var list = from proc in en.Product
join cat in en.Category on proc.catid equals cat.catid into catList
from cl in catList.DefaultIfEmpty()
select new{
id = prod.id,
name=prod.name,
category=cl.name
}
I hope it helps.