As a developer you must learn that knowing to manipulate data in your applications is a must. There are several different techniques to access data sources such as XML files, text files, excel files, databases etc, and LINQ is one of them. These series of posts will give you code examples showing how to use LINQ to Objects, LINQ to SQL and finally LINQ to Entities. I will not write anything about how LINQ works behind the scenes but instead I am going to give practical examples so you can use similar in your applications. Let’s start. All you ‘ll need to run the following code is a console application. So create one and open the Program.cs file.
Suppose you have a Developer class with properties “Name”, “Language” and “Age”.
public class Developer
{
public string Name;
public string Language;
public int Age;
}
In you main method create an array list of Developer type items. Let’s say you want to retrieve those whose language is equal to C#. The following code will output Chris and Fred’s name.
Developer[] developers = new Developer[] {
new Developer {Name = "Chris", Language = "C#"},
new Developer {Name = "Nick", Language = "Visual Basic"},
new Developer {Name = "Fred", Language = "C#"}};
IEnumerable developersWritingCSharp =
from d in developers
where d.Language == "C#"
select d.Name;
foreach (string item in developersWritingCSharp)
{
Console.WriteLine(item);
}
If you wanted to use an ArrayList type instead of a simple array, you should provide the type of the range variable “d” in the from clause. This is because an ArrayList objects of type Developer isn’t a strongly type data source. So you should write something like that.
ArrayList developers = new ArrayList();
developers.Add(new Developer { Name = "Chris", Language = "C#" });
developers.Add(new Developer { Name = "Nick", Language = "Visual Basic" });
developers.Add(new Developer { Name = "Fred", Language = "C#" });
IEnumerable developersWritingCSharp =
from Developer d in developers
where d.Language == "C#"
select d.Name;
You can use multiple From clauses to join multiple data sources. Suppose you have a Customer class which has some properties and one of them is an array of Orders. Create the following classes.
public class Customer
{
public String Name { get; set; }
public String City { get; set; }
public Order[] Orders { get; set; }
}
public class Order
{
public Int32 IdOrder { get; set; }
public Decimal EuroAmount { get; set; }
public String Description { get; set; }
}
In your main method create some customers. In case you wanted to retrieve all orders for each customer you would write:
Customer[] customers = new Customer[] {
new Customer { Name = "Chris", City = "Athens",
Orders = new Order[] {
new Order { IdOrder = 1, EuroAmount = 100, Description = "Order 1" },
new Order { IdOrder = 2, EuroAmount = 150, Description = "Order 2" },
new Order { IdOrder = 3, EuroAmount = 230, Description = "Order 3" },
}},
new Customer { Name = "John", City = "London",
Orders = new Order[] {
new Order { IdOrder = 4, EuroAmount = 320, Description = "Order 4" },
new Order { IdOrder = 5, EuroAmount = 170, Description = "Order 5" },
}}};
var ordersQuery =
from c in customers
from o in c.Orders
select new { c.Name, o.IdOrder, o.EuroAmount };
foreach (var item in ordersQuery)
{
Console.WriteLine(item);
}
The first From clause retrieve all Customer objects and the second retrieve all orders applying to previous retrieved customers. So, for a specific “c” Customer in customers, c.Orders defines the orders array list of that object. Suppose now, want to retrieve for all customers, only those orders with EuroAmount > 100. You must use a Where clause now defining that constraint.
var ordersQuery = from c in customers
from o in c.Orders
where o.EuroAmount > 100
select new { c.Name, o.EuroAmount };
You can end a LINQ query either with Select or Group clause. Add some more Developers in the array and try to group them by language. Notice that groups returned by the Group clause, can be identified by a key.
Developer[] developers = new Developer[] {
new Developer { Name = "Chris", Language = "C#" },
new Developer { Name = "Joy", Language = "C#" },
new Developer { Name = "Alex", Language = "Visual Basic" },
new Developer { Name = "Nick", Language = "Fortran" },
new Developer { Name = "George", Language = "SQL" },
new Developer { Name = "Aris" , Language = "Fortran"}
};
var developersGroupedByLanguage = from d in developers
group d by d.Language;
foreach(var group in developersGroupedByLanguage) {
Console.WriteLine("Group {0}:", group.Key);
foreach (Developer d in group)
{
Console.WriteLine(d.Name);
}
}
Suppose you want to count how many developers know a specific language. After grouping the developers with the Group clause, you can use the Into clause to store the Group’s clause result in a temporary variable. You can use this variable later to execute additional queries.
Developer[] developers = new Developer[] {
new Developer { Name = "Chris", Language = "C#" },
new Developer { Name = "Joy", Language = "C#" },
new Developer { Name = "Alex", Language = "Visual Basic" },
new Developer { Name = "Nick", Language = "Fortran" },
new Developer { Name = "George", Language = "SQL" },
new Developer { Name = "Aris" , Language = "Fortran"},
};
var developersGroupedByLanguage = from d in developers
group d by d.Language into groupedByLang
select new
{
Language = groupedByLang.Key,
Count = groupedByLang.Count()
};
foreach (var group in developersGroupedByLanguage)
{
Console.WriteLine("Language {0} : Contains {1} developers", group.Language, group.Count);
}
The OrderBy clause lets you sort the result of your queries in ascending or descending order. Create some customers with orders and try to sort all orders based on the EuroAmount property.
Customer[] customers = new Customer[] {
new Customer { Name = "Chris", City = "Athens",
Orders = new Order[] {
new Order { IdOrder = 1, EuroAmount = 100, Description = "Order 1" },
new Order { IdOrder = 2, EuroAmount = 150, Description = "Order 2" },
new Order { IdOrder = 3, EuroAmount = 230, Description = "Order 3" },
}},
new Customer { Name = "John", City = "Thessaloniki",
Orders = new Order[] {
new Order { IdOrder = 4, EuroAmount = 320, Description = "Order 4" },
new Order { IdOrder = 5, EuroAmount = 170, Description = "Order 5" },
}}};
var ordersByEuroAmount = from c in customers
from o in c.Orders
orderby o.EuroAmount
select new { c.Name, o.EuroAmount };
foreach (var item in ordersByEuroAmount)
{
Console.WriteLine(item);
}
Let’s see now how join functions in LINQ. Create two classes, one to hold our Product items and one for Category items. Each product is assigned to a category.
public class Category
{
public Int32 IdCategory { get; set; }
public String Name { get; set; }
}
public class Product
{
public String IdProduct { get; set; }
public Int32 IdCategory { get; set; }
public String Description { get; set; }
}
Now create some Category items and then some Product items. Make sure to assign the IdCategory property of each product to an existing Category IdCategory. If you want to query all products by category you can you the join clause:
Category[] categories = new Category[] {
new Category { IdCategory = 1, Name = "Car"},
new Category { IdCategory = 2, Name = "Bike"},
new Category { IdCategory = 3, Name = "Motorcycle"},
};
Product[] products = new Product[] {
new Product { IdProduct = "Car01", IdCategory = 1, Description = "Volvo" },
new Product { IdProduct = "Car02", IdCategory = 1, Description = "Audi" },
new Product { IdProduct = "Car03", IdCategory = 1, Description = "Citroen" },
new Product { IdProduct = "Bike01", IdCategory = 2, Description = "Apollo" },
new Product { IdProduct = "Bike02", IdCategory = 2, Description = "Avanti" },
new Product { IdProduct = "Moto01", IdCategory = 3, Description = "Yamaha" },
new Product { IdProduct = "Moto02", IdCategory = 3, Description = "Honda" },
};
var productsByCaterory = from c in categories
join p in products
on c.IdCategory equals p.IdCategory
select new { c.IdCategory, c.Name, p.Description };
foreach (var item in productsByCaterory)
{
Console.WriteLine(item);
}
That kind of join (inner) maps the outer data source elements (categories) with the corresponding inner data source (products). If you are confused using either the multiple From clause or the Join clause, go and replace the above query with the following which uses multiple From clauses. This definitely, isn’t what you want to get.
var productsByCaterory = from c in categories
from p in products
select new { c.IdCategory, c.Name, p.Description };
Let’s say you want to group your products by category. You can use the Into clause after the Join to group these products. Notice that the Into clause will group items of Product in the variable “CategoryProducts”.
var productsByCategory = from c in categories
join p in products
on c.IdCategory equals p.IdCategory
into CategoryProducts
select new
{
c.IdCategory,
CategoryName = c.Name,
Products = CategoryProducts
};
foreach (var item in productsByCategory)
{
Console.WriteLine("Category ID {0} - Category {1}:", item.IdCategory, item.CategoryName);
foreach (var product in item.Products)
{
Console.WriteLine(product.Description);
}
}
If you want to store the result of a sub-expression in a variable you can use the Let clause. Suppose that you want to retrieve the number of products assigned to a category.
Category[] categories = new Category[] {
new Category { IdCategory = 1, Name = "Car"},
new Category { IdCategory = 2, Name = "Bike"},
new Category { IdCategory = 3, Name = "Motorcycle"},
};
Product[] products = new Product[] {
new Product { IdProduct = "Car01", IdCategory = 1, Description = "Volvo" },
new Product { IdProduct = "Car02", IdCategory = 1, Description = "Audi" },
new Product { IdProduct = "Car03", IdCategory = 1, Description = "Citroen" },
new Product { IdProduct = "Bike01", IdCategory = 2, Description = "Apollo" },
new Product { IdProduct = "Bike02", IdCategory = 2, Description = "Avanti" },
new Product { IdProduct = "Moto01", IdCategory = 3, Description = "Yamaha" },
new Product { IdProduct = "Moto02", IdCategory = 3, Description = "Honda" },
};
var numberOfProductsByCategory = from c in categories
join p in products
on c.IdCategory equals p.IdCategory
into productsByCategory
let numberOfProducts = productsByCategory.Count()
select new
{
c.IdCategory,
numberOfProducts
};
foreach (var item in numberOfProductsByCategory)
Console.WriteLine("Category ID : {0} - Number of Products : {1}", item.IdCategory, item.numberOfProducts);
This was an entry lesson to LINQ basics. There are incredible things you can accomplish using LINQ queries and we are going to see most of them in later posts, so stay in touch!
Categories: ADO.NET
chsakell's Blog 
Good article . One thing I’d definitely mention early on when people are using LINQ is the idea of deferred execution. Although you assign your first query to an IEnumerable (which is fine), I think it’s actually an IQueryable. It means the query doesn’t actually execute until you attempt to enumerate it…. i.e. in the foreach(…) block. Although it’s less of an issue in the above Linq-to-objects example, it becomes very important when DBConnectionScope and potential N+1 Sql calls come into play.
Hi Eoin, I will agree with you that deferred execution means that the evaluation of an expression is delayed until its realized value is actually required. As far as about IQueryable and IEnumerable you have misunderstood. Consider that the first one inherits the latter. So basically gets all the functionality. Still there are many differences between them that makes each of them suitable for particular scenarios. I will mention only three major differences they have. The delegate IQueryable extension methods receive an expression tree while the latter receives a method to invoke. IQuerable allows manipulation of out-of-memory data (databases, web services) while IEnumerable is used for in-memory collections (iterated in memory). The last difference I should mention is that with IQuerable you can move between the items while with IEnumerable you cannot. To sum up, since in the examples of this post we use mock data, there is no need to use the IQuerable interface.