LINQ queries Basics

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);
                }
            }

groupedDevs

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);
            }

countdevsbylang

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);
            }

orderby

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);
            }

joincatproducts

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);
                }
            }

groupcatprod

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!

Advertisements


Categories: ADO.NET

4 replies

  1. 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.

Trackbacks

  1. LINQ to Objects queries | chsakell's Blog
  2. LINQ to SQL : Data Modeling – Inheritance – Relationships | chsakell's Blog

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

Sriramjithendra Nidumolu

Personal Notes of Sriramjithendra

%d bloggers like this: