jump to navigation

LINQ ordering and grouping keywords August 26, 2008

Posted by fofo in c# 3.0, LINQ, Visual Studio 2008.
Tags: , ,
trackback

In this post I will look into ordering and grouping keywords in LINQ.  I will try to explain C# keywords such as orderby, descending, group by .

It is always useful in order to better follow what is going on to have a good understanding what LINQ is, what problems came to address and what are extension methods, anonymous types and in general the C# 3.0 enhancements. I will try to explain everything with a detailed LINQ to SQL (instead of writing sql queries against the db we will do the same with c#) example in C#.

We will need a database for our project so please go and grab the Pubs database. You can download the database from this website.

Click here if you need help with installing the database.

1) Launch Visual Studio 2008

2) Create an ASP.NET Web application project in C#.

3) In the Default.aspx page drag and place on it two web server controls:  a button and just below it a Gridview.

4) From your Server Explorer Window connect to the Pubs database so you can see the database objects like its tables, views e.t.c

5) Add a new item to your project LINQ to SQL Classes and give it the name Pubs.dbml

6) From your Server Explorer Window drag and drop the “Authors” table into the Pubs.dbml surface area. Behind the scenes a C# wrapper maps my database table into a class, I can later reference and use.

7) If you visit the pubs.designer.cs file in your Solution Explorer you will see this line of code

(public partial class pubsDataContext ….).

The database object is now a class with this specific name so I can create an instance of the class and query the database

8) Double click on the button in the Default.aspx page and in the button click event handler add this code

   

pubsDataContext mydb = new pubsDataContext();

 var myauthors = from a in mydb.authors
 select a;

GridView1.DataSource = myauthors;

            GridView1.DataBind();

9) Run your application by hitting F5 from your keyboard. In the gridview you will see all the records from the authors table.

10) Let’s examine the ordering keywords I mentioned above. How can I get the results ordered by the author’s last name?

Replace these two lines of code

var myauthors = from a in mydb.authors
 select a;

 

with these

 var myauthors = from a in mydb.authors
                            orderby a.au_fname
                            select a;

Run your Project again and see the results.

11) If you want to order your authors by their last name in descending order just change this line of code

orderby a.au_fname

with this one

orderby a.au_fname descending

and run your application again.

12) If we wanted to order our results that we take back from the LINQ query by state first and then by author lastname we will write this snippet of code

 var myauthors = from a in mydb.authors
                            orderby a.state ,a.au_fname descending
                            select a;

13) To demonstrate the group keywords we will add new .aspx page in our project.

Name it Default1.aspx. Make this page the Start page by right-clicking on it and selecting the appropriate option

14) Drag the sales table from the Server Explorer in the pubs.dbml surface. We want to group the sales table by order date (ord_date=table field) and see what quantities (qty=table field) for all the book titles were ordered on that day.

15) Add a button to this page.Double click on it. In the event handler add this code

 pubsDataContext mydb = new pubsDataContext();

            var mysales = from s in mydb.sales
                          group s by s.ord_date;

            foreach (var salesgroup in mysales)
            {

                Response.Write(salesgroup.Key.ToShortDateString());
               Response.Write(“<br/>”);
               Response.Write(“<br/>”);

                foreach (var thesale in salesgroup)
                   Response.Write(“–” + thesale.qty);

                Response.Write(“<br/>”);
                Response.Write(“<br/>”);

            }

In the first foreach statement I loop throgh a set of sales(mysales) and print the Key to the salesgroup which in our case is the order date.

In the second foreach statement I want to print on the screen all the sales (thesale) from the salesgroup displaying the quantity.

Run your application and see the results.By any given date we can see the quantities for each book title that has been sold on that date.

In one of my next posts, I will cover the extension methods such as OrderBy(), ThenBy(), OrderByDescending(), ThenByDescending() and GroupBy()

Hope it helps!!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Advertisements

Comments»

No comments yet — be the first.

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

%d bloggers like this: