Geeks With Blogs
Max's Blog .NET, Windows Phone, SQL Server and other exciting stuff

Now that we have some basics for LINQ for XML. Lets get deep into some using LINQ against Relational databases, aggregate functions, like group by and sort. The database we will be using is books, this can be obtained from this page. Once you download the database, mount it in SQL Server by attaching this mdf file. Once the mdf file is mounted, we can start with the following steps to do our first LINQ to SQL console program.

1) First of all we need to add a new DB by selecting Tools > Connect to Database in VS2008 SP1. And then in the dialog box, we need to specify the connection string parameters.

New DB Connection

If you are using the DB in your local computer, use (local) for the server name and then give your log on credentials if you’ve setup SQL Authentication. Then you can select the DB you want to connect to in the Connect to DB section. Click OK and its all done.

2) Then right click on the project in Solution explorer and then add LINQ to SQL class item, name the file as Books. Now you will have a new LINQ to SQL designer window open. Now from the database explorer, drag the tables “title_author”, “title”, “author”.

As the tables are not setup with FK constraints, you would not see the relations between the tables, if the table had some FKeys, you would see the relations between the tables in the designer as a line between tables. Save the changes now.

3) Once you’ve done with creating your LINQ to SQL classes. You can create an instance of the BooksDataContext like

BooksDataContext books = new BooksDataContext();

Now let us find out all the books in the type of “children”

IEnumerable<title> titles = from p in books.titles
                                        where p.type.Equals("children")
                                        select p;

Here we get the list of children rows in a IEnumerable collection, which can be iterated through easily. All the columns in the titles table can be easily accessed as properties for the each of the item in the titles collection.

Now that we’ve the rows in titles table with type as children, we can then easily iterate through it and get the details.

foreach (title t in titles)
            {
                Console.WriteLine("Type: " + t.type + ", Title: " + t.title_name);
            }

Now let us try to get the list of books grouped by the type.

var title1 = from p3 in books.titles
                         group p3 by p3.type into g
                         select new { Value = g.Key, Total = g };

The output will be

image

We group by a particular column value using group p3 by p3.type into g, here g.Key will give you the distinct values in that column by which the values are grouped. g as a whole will have a collection of items for each of the key.

We can then easily iterate through the list to print the details as given below.

foreach (var g in title1)
            {
                Console.WriteLine("Type: " + g.Value);
                foreach (var n in g.Total)
                {
                    Console.WriteLine("Title: " + n.title_name);
                }
                Console.WriteLine();
            }

This would give us a pretty output as below:

image

You can get the final code file here. Wow! That was cool. Imagine writing SQL Queries via C#!

Technorati Tags:
Posted on Tuesday, December 22, 2009 11:58 PM LINQ | Back to top


Comments on this post: LINQ Part 3 – LINQ for SQL

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Max | Powered by: GeeksWithBlogs.net