Thursday, 8 March 2012

Outer Join with Linq SharePoint 2010

With help from http://www.dotnetmafia.com/blogs/dotnettipoftheday/archive/2008/10/15/my-500th-post-left-outer-joins-with-linq.aspx, this is my outer join linq



var outerX = from cust in dataContext.Customers.ToList()
join ord in dataContext.Orders.ToList()
on cust.CustomerID.ToString().Trim()
equals ord.CustomerID.CustomerID.ToString().Trim()
into CustomerInformationGroup
from item in CustomerInformationGroup.DefaultIfEmpty(
new OrdersItem { Title="-",OrderID=0,OrderDate=DateTime.Now,Quantity=0,ProductID=new ProductsItem(),CustomerID=new CustomersItem()})
select new
{
CustomerID = cust.CustomerID,
Title = cust.Title,
City = cust.City,
CustomerCountry = cust.CustomerCountry,
JoiningDate = cust.JoiningDate,
OrderTitle = item.Title
};

dataGrdCustomer.DataSource = outerX.ToList();
dataGrdCustomer.Columns.Clear();

DataGridViewTextBoxColumn custName = new DataGridViewTextBoxColumn();
custName.DataPropertyName = "Title";
custName.Name = "Title";
custName.HeaderText = "Name";

dataGrdCustomer.Columns.Add(custName);

DataGridViewTextBoxColumn custID = new DataGridViewTextBoxColumn();
custID.DataPropertyName = "CustomerID";
custID.Name = "CustomerID";
custID.HeaderText = "CustomerID";
dataGrdCustomer.Columns.Add(custID);

DataGridViewTextBoxColumn custCity = new DataGridViewTextBoxColumn();
custCity.DataPropertyName = "City";
custCity.Name = "City";
custCity.HeaderText = "City";
dataGrdCustomer.Columns.Add(custCity);

DataGridViewTextBoxColumn custCountry = new DataGridViewTextBoxColumn();
custCountry.DataPropertyName = "CustomerCountry";
custCountry.Name = "CustomerCountry";
custCountry.HeaderText = "Country";
dataGrdCustomer.Columns.Add(custCountry);

DataGridViewTextBoxColumn prodTitle = new DataGridViewTextBoxColumn();
prodTitle.DataPropertyName = "OrderTitle";
prodTitle.Name = "OrderTitle";
prodTitle.HeaderText = "OrderTitle";
dataGrdCustomer.Columns.Add(prodTitle);


Here's the output table,

No comments:

Post a Comment