LINQ for Beginners

Topic covered are :

• WHERE clause in LINQ
• LEFT JOIN in LINQ
• INNER JOIN in LINQ
• GROUP BY in LINQ
• ORDER BY in LINA

Script used is mentioned below:

from i in Customers select i

from i in Customers select new
{
i.CustomerID,
i.CompanyName,
i.ContactName
}
from i in Customers
select  new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
            select new { p.EmployeeID , p.OrderDate, p.ShipName }
}
//WHERE clause
from i in Customers
where (i.CustomerID=="ALFKI")
select  new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
            select new { p.EmployeeID , p.OrderDate, p.ShipName }
}
from i in Customers
where i.CustomerID.Contains("A")  
select  new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
            select new { p.EmployeeID , p.OrderDate, p.ShipName }
}
//OR and AND operator
from i in Customers
where i.CustomerID.StartsWith("A") &&  i.CustomerID.EndsWith("A")
select  new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
            select new { p.EmployeeID , p.OrderDate, p.ShipName }
}
from i in Customers
where (i.CustomerID.EndsWith("A"))
select  new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
            select new { p.EmployeeID , p.OrderDate, p.ShipName }
}
//NOTE: Select C# Statement(s)
// SQL IN clause
string[] CustomerList = {"FISSA","HILAA","ALFKI"};
var data=from i in Customers
where CustomerList.Contains(i.CustomerID)
select new
{
i.CustomerID,
i.CompanyName,
i.ContactName
};
foreach(var i in data)
{
Console.Write(i.CompanyName + "\n");
}
//Using orderby
var data=from i in Customers
        where(i.CompanyName.StartsWith("A"))
        orderby i.CompanyName ascending,i.CustomerID descending
        select new
    {
    i.CustomerID,
    i.CompanyName,
    i.ContactName
    };
foreach(var i in data)
{
Console.Write("ID: " + i.CustomerID.ToString() + " Company Name: " + i.CompanyName + " Contact Name: " + i.ContactName.ToString() + "\n");
}

//Using Take and Skip for Paging
(from i in Customers
orderby i.CompanyName descending
select new
    {
    i.CustomerID,
    i.CompanyName,
    i.ContactName
    }).Take(5)
(from i in Customers
orderby i.CompanyName descending
select new
    {
    i.CustomerID,
    i.CompanyName,
    i.ContactName
    }).Skip(5).Take(5)
    
//More Conditions
from i in Customers
select new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
        where p.Freight>1000
            select new {p.OrderDate, p.OrderID,Amount =p.Freight}
}

from i in Customers
where i.Orders.Any(p=>p.Freight>1000)
select new
{
i.CustomerID,
i.CompanyName,
Orders=from p in i.Orders
        where p.Freight>1000
            select new {p.OrderDate, p.OrderID,Amount =p.Freight}
}

from  i in Customers
let ds=from p in i.Orders
        where p.Freight>1000
        select new { p.OrderID,p.OrderDate,Amount=p.Freight }
where ds.Any()
select new {i.CustomerID,i.CompanyName,Orders=ds}

//Converting Anonymous type to List
from  i in Customers
let ds=(from p in i.Orders
        where p.Freight>1000
        select new { p.OrderID,p.OrderDate,Amount=p.Freight }).ToList()
where ds.Any()
select new {i.CustomerID,i.CompanyName,Orders=ds}

//CROSS Join
from i in Customers
from p in Orders
select i.CompanyName + p.OrderID.ToString()

//Join using WHERE
from i in Customers
from p in Orders
where i.CustomerID==p.CustomerID
select i.CompanyName + " Order " + p.OrderID.ToString()

//INNER Join
from i in Customers
join p in Orders on i.CustomerID equals p.CustomerID
select i.CompanyName + " Order " + p.OrderID.ToString()

//INNER Join
from i in Customers
from p in i.Orders
select i.CompanyName + " Order " + p.OrderID.ToString()

//Inner Join 3 Level
from i in Customers
from o in i.Orders
from od in o.OrderDetails
join  p in Products on od.ProductID equals p.ProductID
orderby o.OrderID
select i.CompanyName + " Order " + o.OrderID.ToString() + " Product " + p.ProductName

//Lazy Binding
from i in Customers
select new {i.CompanyName,Order=i.Orders.ToList()}

//DefaultIfEmpty()
from i in Customers
from p in i.Orders.DefaultIfEmpty()
select new {i.CompanyName,Amount=(Decimal?)p.Freight}

//Group By
from i in Customers
where i.Orders.Count>15
select i

from i in Orders
group i.Freight by i.CustomerID into Sales
select new
{
Key=Sales.Key,
Amount=Sales.Sum()
}

from i in Customers
from p in i.Orders
group p.Freight by new {i.CompanyName,i.CustomerID } into Sales
select new
{
key=Sales.Key,
Amount=Sales.Sum(),
Count=Sales.Count()
}