Naveen's Weblog

Bridge to future

LINQ with best example – 2

Posted by codingsense on January 12, 2009

Hi friends,
In my previous post LINQ with best example – 1 , we had seen the basic concepts of linq with the company sample. Today we will move a bit furthur and see how LINQ can be used for an project handling Inventory.


The Inventory Sample

Download source code – 8 Kb

In inventory we will create 3 classes namely Customer, Item, and Sales. The Class diagram of the 3 classes are shown below.


Class Diagram

Create Customers, Items and Sales:
First let us insert some dummy records into the list of customer, Items and sales.

List<Customer> ListOfCustomer = new List<Customer>();
Customer C1 = new Customer() { Name = "Naveen", Area = "Mahalaxmi", City = "Bangalore", ID = 1 };
Customer C2 = new Customer() { Name = "Aatish", Area = "Layout", City = "Bangalore", ID = 2 };
Customer C3 = new Customer() { Name = "Manas", Area = "Layout", City = "Bangalore", ID = 3};
Customer C4 = new Customer() { Name = "Raghu", Area = "Mahalaxmi", City = "Bombay", ID = 4 };
ListOfCustomer.AddRange(new Customer[]{C1,C2,C3,C4});

List<Item> ListOfItems = new List<Item>();
Item I1 = new Item() { ID = 1, Name = "Monitor", Quantity = 15, UnitPrice = 5000 };
Item I2 = new Item() { ID = 2, Name = "Processor", Quantity = 10, UnitPrice = 10000};
Item I3 = new Item() { ID = 3, Name = "Keyboard", Quantity = 25 ,UnitPrice = 1000};
ListOfItems.AddRange(new Item[]{I1,I2,I3});

List<Sales> ListOfSales = new List<Sales>();
Sales S1 = new Sales() { CustomerID = 1, ItemID = 1, Quantity = 5 };
Sales S2 = new Sales() { CustomerID = 1, ItemID = 2, Quantity = 3 };
Sales S3 = new Sales() { CustomerID = 2, ItemID = 1, Quantity = 5 };
Sales S4 = new Sales() { CustomerID = 2, ItemID = 3, Quantity = 5 };
Sales S5 = new Sales() { CustomerID = 3, ItemID = 2, Quantity = 5 };
Sales S6 = new Sales() { CustomerID = 3, ItemID = 3, Quantity = 15};
ListOfSales.AddRange(new Sales[]{S1,S2,S3,S4,S5,S6});

Using the above classes we will query the following

  • List all customers
  • List customers from a specific city
  • No of customers in each city
  • No of items purchased by each customer
  • No of Items remaining in inventory
  • Amount received from each item
  • Amount paid by each customer

List all customers:
For listing the customers use the following code.

foreach (Customer c in ListOfCustomer)
Console.WriteLine(c.Name + "," + c.ID + "," + c.Area + "," + c.City);

List customers from a specific city:
For filltering the customer depending on a specific city you can use

var CustomerCity = from customer in ListOfCustomer
where customer.City == "Bangalore"                               
select new                               
Name = customer.Name,
ID = customer.ID

You can also use where customer.City.StartsWith(“Ba”) to filter a customer when there is an entry in Quick search box.

No of customers in each city:
For finding the No. of customers from each city we have to group the customers on key ‘CITY’.

var CustomersInCity = from customer in ListOfCustomer
group customer by customer.City into GroupedCustomer
select new                               
City = GroupedCustomer.Key,
EmployeeCount = GroupedCustomer.Count()

No of items purchased by each customer:
For searching the items purchased by each customer we shall join all 3 lists and derive the output.

var CustomerItem = from cust in ListOfCustomer
join sale in ListOfSales 
on cust.ID equals sale.CustomerID 
join item in ListOfItems
on sale.ItemID equals item.ID 
select new { CustName = cust.Name, ItmName = item.Name, Qnt = sale.Quantity };

No of Items remaining in inventory:
For finding the remaining items in inventory we shall group the items in sales with itemid key. After which, we get quantity of each item sold. Then we will join the output of ItemID and SoldItems with Items in which we will get the No of items purchased. Subtracting Initial Count with Sold Count we get the items in Inventory.

var RemainingInventory = from itm in ListOfItems
join sold in
from sale in ListOfSales
group sale by sale.ItemID into GroupedItemInSale
select new                                          
ID = GroupedItemInSale.Key,
Qty = GroupedItemInSale.Sum(sold => sold.Quantity)
on itm.ID equals sold.ID
select new { itm.Name, RemQnt = itm.Quantity - sold.Qty };

Amount received from each item:
For getting the amount received from each item, first we require the total no of each item sold, so we shall group the sales on ItemId. By this we get the ItemID and no of items sold. Then the unit Pice of each item can be multiplied by the no of peices sold by joining the first query output with Item List.

var IncomeFromSales = from item in ListOfItems
join sold in
from sale in ListOfSales
group sale by sale.ItemID into GroupedItems
select new                                          
ID = GroupedItems.Key,
Quantity = GroupedItems.Sum(GI => GI.Quantity)
on item.ID equals sold.ID 
select new { Name = item.Name, AmountRec = item.UnitPrice * sold.Quantity };

Amount paid by each customer:
For this first let us analyze what is required and then split it till we go to the solution
1) Customer Name with Total Amount – the final output we require is Customer Name and Total Amount
2) ListOfcustomer, (Customer ID, each item quantity , each item price) – Customer Name can be fetched by joining LIstofcustomer with an output that has (customerID, Quantity of each item purchased and item price for each)
3) ListOfCustomer, Joining(Item and Sales) – to attain the output for (2) can be attained if we join Item and sales.

So now reverse the process and the following query will be acheived.

var AmountPaidByCustomers = from cust in ListOfCustomer
join GroupedAmount in
(from GroupAmount in
(from itm in ListOfItems
join sale in ListOfSales
on itm.ID equals sale.ItemID
select new                                              
Amount = sale.Quantity * itm.UnitPrice
group GroupAmount by GroupAmount.CustomerID into GroupedAmount
select new                                      
{CustomerID = GroupedAmount.Key,
Amount = GroupedAmount.Sum(i => i.Amount)
on cust.ID equals GroupedAmount.CustomerID
select new                              

The above analysis helped in identifying how to proceed, So the steps followed to attain the final output
1) Join Sales and Item -> Get CustomerID, Amount = ItemID * Unit Pice ( this will be the amount for each type of item purchased)
2) Group the output of (1) on CustomerID -> Gets CustomerID, Amount ( this will be the total amount Purchased by the customer)
3) Join result of (2) with LIstOfCustomer on ID -> Gets Customer Name and Amount

Output :

Final Output

Final Output



By the last problem we come to know that problem are simple to solve, but you should analyze the problems and split it up in simple steps.
Hope you have understood the Inventory sample, if you have any doubts or want a different sample then you can post a comment.


Happy Learning 🙂


2 Responses to “LINQ with best example – 2”

  1. Asad said

    Many Many thanks. U have made “Happy Learning” in real sense. Great job.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: