Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
BRH 12
#DOTNET 10
#LINQ 6
LINQ 5
#ASP.NET 4
#DATAACCESS 4
ASP.NET 3
DOTNET 3
.NET 2
WCF 2

Archive · View All
July 2010 5
August 2010 4
June 2011 3
October 2010 1

LINQ to SQL Query Operators Part I

Jul 31 2010 11:47PM by IBhadelia   

What we learned previously

In the previous article, were had an introduction to LINQ. Let’s summarize what we learned about LINQ so far. LINQ stands for Language Integrated Query which was introduced in .Net Framework 3.5. So far C# 3.0 and Visual Basic 9.0 languages support query syntax that allows you to write a query on a data source in a similar way you would write a query in SQL. With other languages, extension methods must be used to use LINQ. LINQ is used to query data from multiple data sources such as CLR collections, Databases and XML using a similar syntax. Now, let’s start exploring the basics of LINQ and how you can start using LINQ.

Introduction

Just like any language syntax, LINQ provides a set of operators. For example, Select, Where, OrderBy, From, etc. All these operators can be divided into different categories. We can easily assume what some of them do like Select and Where. Many others are new operators and may require a little learning and practice to get familiar with like SelectMany and Zip. But overall LINQ has a quite fair learning curve. In this article we are going to learn the Basic Query Operators. These are:

  • From
  • Where
  • Select
  • OrderBy
  • SelectMany
  • Let

NOTE: The code samples in this article were created in C# language using Northwind Database which can be downloaded from here.

From Operator

Every LINQ query must start with the from operator. In fact, there is no from operator in LINQ. Any expression that evaluates to a sequence of objects, can be considered as a from operator. For example, a method that returns an IEnumerable of String can be used as a source to query a set of Strings.

When using language query syntax, we can use a from keyword in C# to define a data source to perform a query :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    select c; 

    foreach (var c in customers)
    {
        Console.WriteLine("Customer : " + c.ContactName);
    }
} 

Output :

image

Here we have defined Customers table as a data source. We can read this in our mind as “for each c, in collection customers, select a sequence of c”. While not using query syntax there is no need of a From Extension Method because we already have access to the collection of data that we want to query. So, the above example can also be written as :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = context.Customers.Select(c => c); 

    foreach (var c in customers)
    {
        Console.WriteLine("Customer : " + c.ContactName);
    }
}

Output :

image

Quite simple if you are familiar with lambda expressions, isn’t it? It does the same thing simply using a different syntax.

Where Operator

In a simple select query, we may not need to filter the data. But, quite frequently, we must filter data in a data source. That’s where Where operator comes in. It’s usage is quite intuitive when you are familiar with SQL query syntax. It requires an expression or a methods of type Predicate. Predicate takes the collection’s type as a parameter and returns a boolean value. True value means the object should be included in the sequence and False return value means it should be rejected.

Let’s look at an example using C# query syntax :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    where c.ContactName.StartsWith("a")
                    select c; 
    foreach (var c in customers)
    {
        Console.WriteLine("Customer : " + c.ContactName);
    }
}

Output :

image

Here we add a condition to our previous example selecting only names that start with letter a. Here, the expression c.FirstName.StartsWith(“a”) is a lambda expression which returns a boolean value which indicates inclusion of a specific object in the sequence returned by this query.

All query operators are defined as Extension Methods in type Enumerable. Where is one of them. So, we can also call the extension method directly which acts like the example shown above :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = context.Customers.Where(c => c.ContactName.StartsWith("a")).Select(c => c); 

    foreach (var c in customers)
    {
        Console.WriteLine("Customer : " + c.ContactName);
    }
}

Output :

image

Other than using a different syntax, both the examples are identical and return the same sequence. Notice how we had to define a lambda expression in the Where method. 

Select Operator

After defining a source of data and a condition, we must define what type of data should be in the sequence that is returned from a LINQ query. Data can be of any type, can have multiple properties and it can also be an anonymous type. We can define the type of sequence using Select Operator. Select Extension Method takes an expression or a method of type Func which is a generic type. It takes one argument which is type of source collection’s type. It returns what should be selected out of an object passed through parameter which may or may not be of object’s type.

Following is an example which shows the usage of Select Operator using C# query syntax :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var names = from c in context.Customers
                select c.ContactName; 

    foreach (var n in names)
    {
        Console.WriteLine("Name : " + n);
    }
}

Output :

image

which can also be written as :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var names = context.Customers.Select(c => c.ContactName); 

    foreach (var n in names)
    {
        Console.WriteLine(n);
    }
}

Output :

image

Here, names is an IEnumerable of type String because the expression in Select Operator evaluates a value of type String. That is why we can directly pass the variable n which is of type String to WriteLine method.

OrderBy Operator

As the name suggests, OrderBy Operator is used to sort the sequence using given expression. It takes an argument of type which is source collection’s type and may return an object of different type. The object evaluated from given expression passed in OrderBy Operator’s expression is then used to sort the sequence.

For example, we can sort our Customers data by their country using C# query syntax by writing the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    orderby c.Country ascending
                    select new { Name = c.ContactName, Country = c.Country }; 

    foreach (var c in customers)
    {
        Console.WriteLine("Name : " + c.Name + ", Country : " + c.Country);
    }
}

Output :

image

Notice that we have used an Anonymous Type in the given example. The Select Operator takes an expression which returns an Anonymous Type with properties Name and Country. Here we provide the OrderBy Operator with an expression which takes a Customer object as input and returns it’s country as output which is used to compare and sort the final result sequence. Notice the usage of ascending keyword which is optional because by default, the sorting is done in ascending order. If we want to sort our data in descending order, we can just replace the ascending keyword with descending keyword. We can also use multiple properties to sort the result data by using a comma separated list of properties. So, for example if we want to sort the result by customer’s country in ascending order first and then by their name in descending order, we would write the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    orderby c.Country ascending, c.ContactName descending
                    select new { Name = c.ContactName, Country = c.Country }; 

    foreach (var c in customers)
    {
        Console.WriteLine("Name : " + c.Name + ", Country : " + c.Country);
    }
}

Output :

image

The OrderBy Operator has four backing Extension Methods that provide the sorting functionality. They are OrderBy, OrderByDescending, ThenBy and ThenByDescending. As their names suggest, OrdeyBy and OrderByDescending methods sort the result in ascending order and descending order respectively. ThenBy and ThenByDescending methods are used to add sorting to the previously defined sorting in ascending order and descending order respectively. We can replace the previous example with the specified Extension Methods by writing the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = context.Customers.OrderBy(c => c.Country).
        ThenByDescending(c => c.ContactName).
        Select(c => new { Name = c.ContactName, Country = c.Country }); 

    foreach (var c in customers)
    {
        Console.WriteLine("Name : " + c.Name + ", Country : " + c.Country);
    }
}

Output :

image 

SelectMany Operator

SelectMany Operator is one of the operators that does not have any equivalent keyword in C# query syntax.  SelectMany Operator is used to flatten multiple result sequences into one single sequence. For example, if we have a query that returns a collection of collections, we can use the SelectMany Operator to merge all the resulting collections into a single collection. Without using SelectMany Operator, we may have to write the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customerOrders = from c in context.Customers
                            select c.Orders; 

    foreach (var orders in customerOrders)
    {
        foreach (var order in orders)
        {
            Console.WriteLine("Customer : " + order.Customer.ContactName + ", Order : " + order.OrderID);
        }
    }
}

Output :

image

Here we use two for each loops to get a list of each customer’s orders. Using the SelectMany Operator we can write the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customerOrders = from c in context.Customers
                            from o in c.Orders
                            select o; 

    foreach (var order in customerOrders)
    {
        Console.WriteLine("Customer : " + order.Customer.ContactName + ", Order : " + order.OrderID);
    }
}

Output :

image

The differences in above mentioned methods are multiple from definitions instead of single from definition and one foreach loop instead of two foreach loops. As mentioned earlier, there in no equivalent keyword in C# query syntax for SelectMany Operator. We can use SelectMany Operator in C# query syntax by using from keyword multiple times. Notice that in above example, the second from defines a Orders collection in a customer’s object as a data source which can be selected in the expression provided to the Select Operator.

There is a SelectMany Extension Method defined which can be used if we don’t want to use the C# query syntax. Using the SelectMany Extension Method, we can replace the above code with the following code :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customerOrders = context.Customers.SelectMany(p => p.Orders); 

    foreach (var order in customerOrders)
    {
        Console.WriteLine("Customer : " + order.Customer.ContactName + ", Order : " + order.OrderID);
    }
}

Output :

image

Here in the expression provided in SelectMany Operator takes a type of Customer as input and returns a collection of type Order as output.

Let Operator

One of the LINQ Operators is the Let operator that does not have an equivalent Extension Method and can only be used in C# query syntax. Let Operator lets you name an expression in a LINQ query which helps you reuse the same expression in multiple places in the same query, makes a query clean and easily readable and makes the query neat and compact. Let Operator can be used to define extra data sources, name expressions or create an alias for sub-properties. For example, if we want to retrieve a list of Customers who have placed at least 1 order but not more 3 orders, we would have to write the following query :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    where c.Orders.Count > 1 && c.Orders.Count < 4
                    select c; 

    foreach (var customer in customers)
    {
        Console.WriteLine("Customer : " + customer.ContactName);
    }
}

Output :

image

Using Let Operator, we can write the following code which does the same thing :

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var customers = from c in context.Customers
                    let orderCount = c.Orders.Count
                    where orderCount > 1 && orderCount < 4
                    select c; 

    foreach (var customer in customers)
    {
        Console.WriteLine("Customer : " + customer.ContactName);
    }
}

Output :

image

Notice how we replaced the c.Orders.Count expression with named variable orderCount. It reuses the same expression, simplifies the query and makes it more readable. This is just a simple example. Imagine a large query with a lot of references to the same expression which is still in a development phase. When an expression is changed, the change needs to be applied to all the places. Using Let Operator, one can avoid this scenario, reuse the same expression, assign a name and a meaning to the expression, make the query neat, compact and more readable.

Summary

In this part, we learned about the basic LINQ Operators. The Operators that we learned are From Operator which defines a data source, Where Operator which is used to define a search criteria, OrderBy Operator which defines a sort order for the result, Select Operator which defines the contents of the result sequence, SelectMany Operator which flattens multiple result sequences into one and Let Operator which is used to create named aliases for an expression. These operators are used to create a basic LINQ query and are most commonly used. After learning how to use them, you can start writing LINQ queries and once getting a good grip on them, you can move on to the more advanced operators of LINQ.

Tags: #DOTNET, LINQ, #DATAACCESS, #LINQ, BRH, DOTNET, DATAACCESS, C#,


IBhadelia
163 · 1% · 299
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]