Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

An abundance of LINQ queries and expressions using both the query and method syntax.

In this post I will be writing LINQ queries against an array of strings, an array of integers.Moreover I will be using LINQ to query an SQL Server database.

I can use LINQ against arrays since the array of strings/integers implement the IENumerable interface.

I thought it would be a good idea to use both the method syntax and the query syntax.

There are other places on the net where you can find examples of LINQ queries but I decided to create a big post using as many LINQ examples as possible.

We will start with very simple queries 

1) Fire up Visual Studio 2008/2010. Express editions will work fine.

2) Create an empty web site. Give it the name of your choice.Choose C# as the development language.

3) Add a web form item to your site. Leave the default name. We will start off with a very simple example.

4) In the Page_Load event handling routine type (Do not comment out the array declaration. Very often in this post you will have to comment out bits of code but not this one)

  string[] names = { "Hilary""George""Paul""Mary""Christine""Fred",
 "John", "Oliver""Ken""Jessy""Joddie""Helen""Wesley""Elvis" };


        foreach (string name in names)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }

 We have our single array and just loop through the items.Run your application.Then comment out the foreach loop.

5) If we want to find all the names in the array that start with the letter "M", we can type in the Page_Load event handling routine,

  foreach (string name in names)
        {
            if (name.StartsWith("M"))
                Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results.

6) Now let's have the same result by writing a LINQ query.Comment out the previous foreach loop and type

        var namesStartWithM = from name in names
                              where name.StartsWith("M")
                              select name;

        foreach (string item in namesStartWithM)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
7) Now let's rewrite the previous query using lambdas 
Comment out the previous foreach loop and the var statement  and type
 var namesStartWithM = names.Where(n => n.StartsWith("M"));

        foreach (string item in namesStartWithM)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }

 Run your application again and see the results.

8) Now let's rewrite the previous query in a much simpler way.Comment out the previous foreach loop and the var statement  and type

        foreach (string name in names.Where(n => n.StartsWith("M")))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }

 This is a much simple way. Run your application again and see the results.

9)  Now let's say we want to find only the names that have more than 6 characters.

Comment out the previous foreach loop and in the Page_Load event handling routine
 foreach (string name in names.Where(n => n.Length>6))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 Run your application again and see the results.We can rewrite the above query using the query syntax.
Comment out the previous foreach loop  and type
  var mynames = from name in names
                where name.Length > 6
                select name;

        foreach (string item in mynames)
        {
            Response.Write(item);
            Response.Write("<br/>");
        }
Run your application again and see the results. 
10)  Now let's say we want to order our array using the query syntax. 
Comment out the previous foreach loop and the var statement in the Page_Load event handling routine type
  var query = from name in names
                    orderby name
                    select name;

        foreach (string name in query)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
11)  Now let's say we want to order our array using the method syntax. 
Comment out the previous foreach loop and the var statement. In the Page_Load event handling routine type
    foreach (string name in names.OrderBy(n => n))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
 
Run your application and see the results.
We can also order our list descending. Change the first line of the loop with this line
foreach (string name in names.OrderByDescending(n => n))
12)  But what if we wanted to get only some elements of the array and ignore the first 4.
Comment out the previous foreach loop. In the Page_Load event handling routine type
   foreach (string name in names.Skip(4))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results. Comment out the previous foreach loop and type
var mynames = from name in names.Skip(4)
                      select name;

        foreach (string name in mynames)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
13)  But what if wanted to take the first four elements of the array only.
Comment out the previous foreach loop and var statement and type
    foreach (string name in names.Take(4))
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
Run your application and see the results.Now let's do the same using the query syntax.Comment out the previous foreach loop and type
 var mynames = from name in names.Take(4)
                      select name;

        foreach (string name in mynames)
        {
            Response.Write(name);
            Response.Write("<br/>");
        }
14) Let's move on and see a more complicated example where we want to have a logical operator (e.g &&). We want to find all names that start with "J" and are more than 4 characters.
Comment out the previous foreach loop and var statement and in the Page_Load event handling routine type 
var mynames = from name in names
                      where name.StartsWith("J") && name.Length > 4
                      select name;


        foreach (string name in mynames)
        {
             Response.Write(name);
           Response.Write("<br/>");
        }
 
Run your application and see the results.  
15) Let's rewrite the above query using the method syntax.Comment out the previous foreach loop  and the var statement. In the Page_Load event handling routine type 
 
   var mynames = names.Where(n => n.StartsWith("J") && n.Length > 4);

        foreach (string name in mynames)
        {
            Response.Write(name);
           Response.Write("<br/>");
        }
 
Run your application and see the results.

16) Now let's see more examples on how to write LINQ queries. I will use LINQ to SQL to demonstrate that.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

17) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

18) From the Server Explorer / Data Explorer window drag the Customers,Orders,Order Details tables and drop them on the Northwind.dbml.

19) Have a look at the generated code from the Linq engine in Northwind.designer.cs

20) Add a new web form to your site.Name it LINQtoSQL.aspx. Drag a Gridview web server control on the LINQtoSQL.aspx page.Let's say we want to return all the customers.

21) In the Page_Load event handling routine of the LINQtoSQL.aspx page type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    select mycust;


        GridView1.DataSource = query;
        GridView1.DataBind();
Note that we return all the fields,columns from the Customers table.If we want to use the method syntax, replace this line of code
var query = from mycust in ctx.Customers
                    select mycust;
with this line of code
var query = ctx.Customers.Select(c => c);
 
Run your application and see the results. 
22) But if we wanted to return only one field,column of the underlying table(Customers) ? Change this line of code
var query = from mycust in ctx.Customers
                    select mycust;
with this one
var query = from mycust in ctx.Customers
                    select mycust.ContactName;
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
                    select mycust.ContactName;  
with this line of code
var query = ctx.Customers.Select(c => c.ContactName);
 
Run your application and see the results. 
23) But what if wanted to have 2 or more fields returned from the Customers table?Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 NorthwindDataContext ctx = new NorthwindDataContext();

 var query = from mycust in ctx.Customers
 select new { mycust.ContactName, mycust.CompanyName, mycust.Country };


 GridView1.DataSource = query;
 GridView1.DataBind();
 
Run your application and see the results.
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
        select new { mycust.ContactName, mycust.CompanyName, mycust.Country };
with this line of code
var query = ctx.Customers.Select(c => new { c.ContactName, c.CompanyName, c.Country });
 
Run your application and see the results.
What we did here was to create a new anonymous type.If you want to see what kind of anonymous type this is, after this line
GridView1.DataBind();
type
Response.Write(query.First().GetType());  
Run your application and see the results.In my case the anonymous type looks like this 
<>f__AnonymousType0`3[System.String,System.String,System.String]

24) Let's imagine we have a custom named type e.g Employee and we wanted to select data into this named type from the anonymous type we just demonstrated before.Add another item in your site, a class file. Name it Employee.cs.Inside the .cs file type 
class Employee
{
 
   
    public string Name { getset; }
 
    public string Company { getset; }

    public string Country { getset; }
 
}
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 NorthwindDataContext ctx = new NorthwindDataContext();

 var query = from mycust in ctx.Customers
 select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,
 Country = mycust.Country };


        foreach (Employee item in query)
        {
            Response.Write(item.Name);
            Response.Write("<br/>");
            Response.Write(item.Company);
            Response.Write("<br/>");
            Response.Write(item.Country);
            Response.Write("<br/>");
        }
Have a look at the code above to see how we select data into a named type from an anonymous type.
Run your application and see the data selected into the Employee type printed out in the screen.
Let's rewrite the query with the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
select new Employee { Name = mycust.ContactName, Company= mycust.CompanyName,
 Country = mycust.Country };
with this line of code
var query = ctx.Customers.Select(mycust => new Employee 
        { Name = mycust.ContactName, Company = mycust.CompanyName, 
Country = mycust.Country }); 
Run your application and see the results.
25) Let's move on to something else. We have orders that are associated with customers. Let's say we want to get only the order date for each order regardless of the customer it is associated with.
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    from order in mycust.Orders
                    select order.OrderDate;

        GridView1.DataSource = query;
        GridView1.DataBind();
 
Run your application and see the results.
26) Let's move on with our example and see how we can filter our results and return a subset of the data. We want to get all the columns of the Customers table, only for them that they live in Germany.
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
 
 NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    where mycust.Country == "Germany"
                    select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of code
var query = from mycust in ctx.Customers
                    where mycust.Country == "Germany"
                    select mycust;
with this line of code
var query = ctx.Customers.Where(mycust => mycust.Country=="Germany");
Run your application and see the results.
27) We want to get the customers that have more than 17 orders. 
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
   NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
                    where mycust.Orders.Count > 17
                    select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.Now let's rewrite the query using the method syntax. Replace this line of code
 var query = from mycust in ctx.Customers
                    where mycust.Orders.Count > 17
                    select mycust;
with this line of code
 
 var query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17);
 
Run your application and see the results.
28) We want to get the customers that have more than 17 orders and live in Germany. 
Comment out everything in the Page_Load event handling routine of the LINQtoSQL.aspx page and then type
NorthwindDataContext ctx = new NorthwindDataContext();

        var query = from mycust in ctx.Customers
        where mycust.Orders.Count > 17 && mycust.Country=="Germany"
        select mycust;

        GridView1.DataSource = query;
        GridView1.DataBind();
Run your application and see the results.If you want to use OR operator just replace the && with || 
Let's use the method syntax.Replace this line of code
var query = from mycust in ctx.Customers
            where mycust.Orders.Count > 17 && mycust.Country=="Germany"
            select mycust;
with this line of code
var query = ctx.Customers.Where(mycust => mycust.Orders.Count > 17 && mycust.Country=="Germany");
 
Run your application and see the results.
29) We want to get the customers and their respective orders. We need to perform some sort of join.Comment out everything in the Page_Load event handling routine and type
 
  NorthwindDataContext ctx = new NorthwindDataContext();

        var query =
        from c in ctx.Customers
        join o in ctx.Orders on c.CustomerID equals o.CustomerID
            into orders
            select new { c.ContactName, OrderCount = orders.Count() };


        GridView1.DataSource = query;
        GridView1.DataBind();
  
Run your application and see the results.
30) Add another web form to your site, name it ArraysOfInt.aspx. Let's investigate more extension methods.
 In the Page_Load event handling routine type 
 
        int[] mynums = { 12, 45, 23, 34, 67, 45, 78, 11, 74, 81, 190, 99, 102,
 109, 112, 116, 27, 121, 65, 134, 19, 154 };

       
        var query = mynums.Any();
   
        
        Response.Write(query);
 
Run your application and see the results. You will get back True. The code above checks if the array has items in it or not.
31) Let's say that we want to check if all the items in the array are greater than 5.
Change the the previous var statement with this one
var query = mynums.All(p => p > 5);
 
Run your application and see the results. You will get back True.
32) If we wanted to check if the value 45 is included in this array. Change the previous var statement with this
 var query = mynums.Contains(45);
Run your application and see the results.
33) What if wanted to find the number of the items in the array? What if wanted to find the sum, the minimum, the maximum, the average, the first, the last values in the array?Comment out the previous var statement and type the following. After you finish with the first var statement, comment it out and follow with the next one.Only one var statement should be uncommented at each time.
 var query = mynums.Count();

var query = mynums.Sum();
var query = mynums.Min();

 var query = mynums.Max();

var query = mynums.Average();

 var query = mynums.First();

var query = mynums.Last();
 
Run your application and see the results.
34) What if wanted to get the first number in the sequence that is greater than 100?Comment out the previous var statement and type
 var query = mynums.First(p => p > 100);
Run your application and see the results.
35) What if wanted to get the last item that is greater than 100? Comment out the previous var statement and type
 var query = mynums.Last(p => p > 100); 
Run your application and see the results.
I could go on and on but I think you have a good view now on how LINQ works are what are the main extension methods.
 Email me if you need the source code.
 Hope it helps!!!
  

Comments

generic said:

Surprisingly! It is like you understand my mind! You seem to know so much about this, just like you wrote the book in it or something. I think that you can do with some pics to drive the content home a bit, but other than that, this is informative blog post. A good read. I’ll definitely revisit again.

# March 23, 2011 9:13 AM

generic viag`З said:

graet site. Keep doing

# March 23, 2011 11:04 AM