In previous post we saw the fundamentals around querying data using LINQ . There are three basic LINQ implementations and this post will give you code examples for the main implementation of the Microsoft Language Integrated Query, LINQ to Objects. In later posts we will also see the LINQ to SQL and LINQ to Entities implementations.
To run the following code simply create a C# console application in Visual Studio. We are going to use only the Program.cs file. Before starting writing our queries, we ‘ll create our classes and variables which are going to hold our objects for LINQ queries. Create the “Customer”, “Order”, “Product” classes and finally add an enumeration to hold the customer’s countries. You can place them above the Program class, under the same namespace.
public class Customer
{
public string Name;
public string City;
public Countries Country;
public Order[] Orders;
public override string ToString()
{
return String.Format("Name: {0} - City: {1} - Country: {2}",
this.Name, this.City, this.Country);
}
}
public class Order
{
public int IdOrder;
public int Quantity;
public bool Shipped;
public string Month;
public int IdProduct;
public override string ToString()
{
return String.Format("IdOrder: {0} - IdProduct: {1} - Quantity: {2} - Shipped: {3} - Month: {4}",
this.IdOrder, this.IdProduct, this.Quantity, this.Shipped, this.Month);
}
}
public class Product
{
public int IdProduct;
public decimal Price;
public override string ToString()
{
return String.Format("IdProduct: {0} - Price: {1}", this.IdProduct, this.Price);
}
public override bool Equals(object obj)
{
if (!(obj is Product))
return false;
else
{
Product p = (Product)obj;
return (p.IdProduct == this.IdProduct && p.Price == this.Price);
}
}
public override int GetHashCode()
{
return String.Format("{0}|{1}", this.IdProduct, this.Price).GetHashCode();
}
}
Now we need to create some mock data. Feel free to create yours if you want, or add more to those I created. I added two private static variables in Program class, to hold the Customer and the Product objects. Then in a static constructor for the Program class, I call an init function which simply assigns my mock data to the variables. This way, everytime you run the application, you make sure your data are initialized.
class Program
{
private static Customer[] customers;
private static Product[] products;
static void CreateMockData()
{
customers = new Customer[] {
new Customer {Name = "Chris", City = "Athens", Country = Countries.Greece, Orders = new Order[] {
new Order {IdOrder = 1, Quantity = 3, IdProduct = 1, Shipped = false, Month = "January"},
new Order {IdOrder = 2, Quantity = 5, IdProduct = 2, Shipped = true, Month = "May"}}},
new Customer {Name = "Alex", City = "Thessaloniki", Country = Countries.Greece, Orders = new Order[] {
new Order {IdOrder = 3, Quantity = 10, IdProduct = 1, Shipped = false, Month = "July"},
new Order {IdOrder = 4, Quantity = 20, IdProduct = 3, Shipped = true, Month = "December"}}},
new Customer {Name = "John", City = "Amsterdam", Country = Countries.Holland, Orders = new Order[] {
new Order {IdOrder = 5, Quantity = 20, IdProduct = 3, Shipped = true, Month = "December"}}},
new Customer {Name = "Joy", City = "Rotterdam", Country = Countries.Holland, Orders = new Order[] {
new Order {IdOrder = 6, Quantity = 20, IdProduct = 5, Shipped = false, Month = "July"}}},
new Customer {Name = "Mary", City = "London", Country = Countries.England, Orders = new Order[] {
new Order {IdOrder = 7, Quantity = 20, IdProduct = 6, Shipped = true, Month = "December"}}},
new Customer {Name = "Sofia", City = "Manchester", Country = Countries.England, Orders = new Order[] {
new Order {IdOrder = 8, Quantity = 20, IdProduct = 7, Shipped = false, Month = "July"}}}};
products = new Product[] {
new Product {IdProduct = 1, Price = 10 },
new Product {IdProduct = 2, Price = 20 },
new Product {IdProduct = 3, Price = 30 },
new Product {IdProduct = 4, Price = 40 },
new Product {IdProduct = 5, Price = 50 },
new Product {IdProduct = 6, Price = 60 },
new Product {IdProduct = 7, Price = 60 },
};
}
static Program()
{
CreateMockData();
}
static void Main(string[] args)
{
}
}
If you examine the code in the “CreateMockData” function you ‘ll see that I have created 6 customers, 2 for each country in the “Countries” enumeration type, and each customer lives in a different city. Also, each customer holds a number of orders which are associated with a Product object in the products variable. Let’s begin now to query data with LINQ.
Let’s say you want to retrieve the names of all Greek customers and the cities they live in. You can write this.
var query = from c in customers
where c.Country == Countries.Greece
select new { c.Name, c.City };
Console.WriteLine("Greek Customers and Citys");
foreach (var item in query)
Console.WriteLine(item);
Quite simple. Let’s try something more interesting. Suppose you want to run the same query, but you want to skip the first record. You can accomplish that using the second signature of the Where operator like this.
var query = customers.Where((c, index) => (c.Country == Countries.Greece && index >= 1)).Select(c => c.Name);
Console.WriteLine("Greek Customers and Citys");
foreach (var item in query)
Console.WriteLine(item);
If you run the query you ‘ll get simply Alex’s name. You have used an index parameter which allowed you to filter your queries. You ‘ll understand better how to use that index with the following example. You have 6 customers in your mock data and you want to get those in the middle, that is the 4th and the 5th one. In my case these are the Holland dudes.
int start = 2; int end = 4; var query = customers.Where((c, index) => (index >= start && index < end)).Select(c => c); foreach (var item in query) Console.WriteLine(item);
Suppose now you want to retrieve the orders that British people have made.
var query = customers.Where(c => c.Country == Countries.England).SelectMany(c => c.Orders);
var sqlquery = from c in customers
where c.Country == Countries.England
from o in c.Orders
select o;
foreach (var item in sqlquery)
Console.WriteLine(item);
Notice that I used the SelectMany operator and not the Select. If you use the latter you ‘ll get an error because the resulting type of this query will be IEnumerable<Order[]> while you need IEnumerable<Order>.
If you want to display Greek customers and their cities ordered by their name, you can use the orderby operator.
var sqlquery = from c in customers
where c.Country == Countries.Greece
orderby c.Name ascending
select new { c.Name, c.City };
var query = customers.Where(c => c.Country == Countries.Greece)
.OrderByDescending(c => c.Name)
.Select(c => new { c.Name, c.City });
foreach (var item in query)
Console.WriteLine(item.Name + " " + item.City);
I gave both the SQL and the Method LINQ syntax. Whichever you use the result will be the same.
Chris Athens Alex Thessaloniki
Let’s try to group our customers now by country. We will use the group operator.
var query = customers.GroupBy(c => c.Country);
foreach (IGrouping<Countries, Customer> group in query)
{
Console.WriteLine("Group: {0}", group.Key);
foreach (var item in group)
Console.WriteLine(item);
}
I pointed the third line to show you that a type of Customer is actually projected to the group. Let’s see now how you can project a different type, for example a string. Suppose we want to retrieve all customer names grouped by country.
var query = customers.GroupBy(c => c.Country, c => c.Name);
foreach (IGrouping<Countries, string> group in query)
{
Console.WriteLine("Country {0}:", group.Key);
foreach (var item in group)
Console.WriteLine(item);
}
Let’s try now to use the join operaton. Suppose you want to get all orders that have referenced Product items from the products variable. I give you both the SQL and the Method syntax.
var query = customers.SelectMany(c => c.Orders)
.Join(products, o => o.IdProduct, p => p.IdProduct,
(o, p) => new { o.IdOrder, o.Month, p.IdProduct, p.Price });
var sqlquery = from c in customers
from o in c.Orders
join p in products
on o.IdProduct equals p.IdProduct
select new { o.IdOrder, o.Month, p.IdProduct, p.Price };
foreach (var item in sqlquery)
Console.WriteLine(item);
What if we wanted to get the associated orders for each product? This is more complicated and you ‘ll need to do it in two seperated steps. First you retrieve all customer orders (orders variable) and the you join it with the products variable.
var orders = from c in customers
from o in c.Orders
select o;
var query = from p in products
join o in orders
on p.IdProduct equals o.IdProduct into customerOrders
select new { p.IdProduct, Orders = customerOrders };
foreach (var item in query)
{
Console.WriteLine("Product {0} :", item.IdProduct);
foreach (var order in item.Orders)
Console.WriteLine(order);
}
Let’s query now all products that have been ordered. Because of the fact that some products are associated with more than one order, we will use the “distinct” function to get an ordered product just once.
var query = (from c in customers
from o in c.Orders
join p in products
on o.IdProduct equals p.IdProduct
select p).Distinct();
foreach (var item in query)
Console.WriteLine(item);
I left the most complicated of all examples for the end. Let’s say we want to calculate the amount of orders for each costumer. First I will calculate each order amount for each costumer (“CustomerOrders”). Then I will join that query result with the costumers variable, projecting the Sum amount for each order.
var CustomerOrders = from c in customers
from o in c.Orders
join p in products
on o.IdProduct equals p.IdProduct
select new { c.Name, Amount = o.Quantity * p.Price };
foreach (var item in CustomerOrders)
Console.WriteLine(item);
Console.WriteLine();
Console.WriteLine("Total Amount by Customer");
Console.WriteLine();
var query = from c in customers
join o in CustomerOrders
on c.Name equals o.Name into CustomerWithOrders
select new { c.Name, TotalAmount = CustomerWithOrders.Sum(o => o.Amount) };
foreach (var item in query)
Console.WriteLine(item);
I hope you enjoyed the examples!
Categories: ADO.NET
chsakell's Blog 
Leave a Reply