LINQ to Objects queries

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

linqobjects1

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

linqobjects2

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

linqobjects3

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

linqobjects4

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

linqobjects5

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

linqobjects6

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

linqobjects7

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

linqobjects8

I hope you enjoyed the examples!

Advertisements


Categories: ADO.NET

Tags: ,

2 replies

Trackbacks

  1. LINQ to SQL : Data Modeling – Inheritance – Relationships | chsakell's Blog
  2. Retrieving data with DbContext « 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

mohitgoyal.co

Automating infrastructure one line at a time

Diary Of A Programmer

Because every day is worth noting

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

%d bloggers like this: