Part 2 In this part we will look at querying relational data.(LINQ)

Post date: Mar 17, 2011 12:41:45 PM

Introduction

This article is based on a single table schema (don’t worry subsequent parts will see a much more complex database schema!) so that you can get to grips with LINQ to SQL (the name for using LINQ with relational data – also known as DLINQ in a previous life, but we won’t go into that...) quickly and feel confident with this awesome new language enhancement.

Hang on! Before you start remember that you will need the March CTP of Visual Studio Orcas, and SQL Server Express 2005 (or the full blown SQL Server 2005) to run all of the sample code in this article. You can download the latest bits from here.

One more thing...what does LINQ stand for? If you don’t know go check out Part 1 and come back!

Conceptual data access layer?

Conceptual what?! If you are familiar with the concept of a database schema then you don’t have much to worry about! You can think of a conceptual data access layer as being your applications view of the database.

I don’t want to bog you down with entities yet! That will come in part 3 of this series. Just know that what we deal with from our applications point of view is a series of entities describing the tables and relationships in our database schema. I will let you look forward to the diagrams explaining all of this in the next part!

Generating our DAL using Visual Studio Orcas

Typically DAL’s have consisted of boilerplate code to map relational data to objects and vice versa, speaking from experience I really enjoyed this the first time I did it – but now it just annoys the heck out of me! With that in mind it’s no surprise that the folks on the Visual Studio team decided to give us a few tools to generate that boilerplate code.

At present there are two tools that come with Visual Studio Orcas (both were in the LINQ May 2006 CTP):

• SQLMetal.exe – a command line tool that gives you a tonne of options when creating your DAL. Things of note are pluralisation of entities, extracting stored procedures from your database schema, and defining the namespace, as well as a tonne of other stuff.

• LINQ to SQL file – used to be called DLINQ objects in the LINQ May 2006 CTP, now it has a cooler name, but it’s pretty much the same tool. This is a file template that lives in Visual Studio Orcas – it’s a designer. Personally I don’t really like stuff that is “drag and drop” so I tend to opt for the finer control offered to me by SQLMetal – with that said it’s a bit of a shock to me that I have opted to use the designer for this part of the series.

Before we go any further I just want to give you a quick snapshot of the table that we will be using for this part of the series – like I mentioned earlier the next part will have several tables with many relationships...think of it as a throwaway schema (actually I don’t think if even qualifies as a database schema it’s so simple!).

Figure 2-1: Our unbelievably simple database schema (for now!)

I bet you are questioning my credibility now aren’t you?! Come on its simple so that you can learn the basics quick so when we look at more complex examples in the future you have a good solid understanding of using LINQ with relational data.

Ok, go ahead and add some dummy data to that table.

Fire up Visual Studio Orcas (the March 2007 CTP remember) and create a new .NET 3.5 Console Application. When you’ve done that right click the project and add a LINQ to SQL File – as all we’ll be throwing away all of the code in this part just name it Part2.dbml for now.

Figure 2-2: Adding a LINQ to SQL File to our solution

At this moment in time we have everything setup so drag the Books table from the server explorer window onto the canvas of Part2.dbml.

Figure 2-3: Result of dragging the Books table onto Part2.dbml

If you take a look at the code that has been generated for us you will notice there is a class called Book that implements a few interfaces (more on those in the next part). Take a look at the attribute that is placed before the class definition (fig. 2-4) here we are associating this type (Book) with Books table in our database.

Figure 2-4: Associating the Book type with our Books table

1.//...

2.[System.Data.Linq.Table(Name="dbo.Books")]

3.//..

Take a moment to look at the fields in the Book type – notice that the designer has created two fields of the same names as the columns in the Books table; properties have also been defined in the class marked with an attribute to associate the fields with their corresponding column in the Books table (Fig. 2-5).

Figure 2-5: Associating the fields in the Book type with the columns in our Books table

01.//...

02.[System.Data.Linq.Column(Storage="_BookId", 

03.  Name="BookId", 

04.  DBType="Int NOT NULL IDENTITY", 

05.  IsPrimaryKey=true, 

06.  IsDBGenerated=true, 

07.  CanBeNull=false)]

08.//...

09.[System.Data.Linq.Column(Storage="_Title", 

10.  Name="Title", 

11.  DBType="NVarChar(50) NOT NULL", 

12.  CanBeNull=false)]

13.//...

Before we move on we will talk a little about the Column attribute (defined in the System.Data.Linq namespace). Take a look at Fig. 2-5, notice that we explicity state the field that stores the value of the column, e.g. the BookId columns’ value is stored in the _BookId field, we also state that this property is associated with the BookId column in the Books table (Name="BookId").

If you are familiar with SQL Server data types then you will also know that there is not a 1..1 mapping between SQL Server 2005 data types and CLR types, e.g. the CLR has no money data type. For this reason we can explicitly state the database column type, the designer will choose the most appropriate type for use for that particular property, e.g. for the BookId column the designer has chosen to use the default int value type (Int32).

Before we move on, just be aware that you have to explicitly state what values are generated by the database, and whether or not they can be null. In the case of BookId the database generates a new integer automatically for each row; this column is also a primary key.

Take some time to further examine the Column and Table attributes.

Querying our DAL

Before you look at the examples to follow please read part 1 of this series to familiarize yourself with how queries are constructed with LINQ.

We will now go through a few simple queries against the DAL that we have created in previous steps.

For each example I have provided the query version and the lambda expression version.

Select all books

Figure 2-6: Selecting all books (query)

01.using System;

02.using System.Linq;

03.  

04.namespace Org.GBarnett.Dns.Linq {

05.  public class Program {

06.    public static void Main() {

07.      Part2DataContext db = new Part2DataContext();

08.      var query = from b in db.Books select b;

09.      foreach (var book in query) {

10.        Console.WriteLine("{0} {1}", book.BookId, book.Title);

11.      }

12.    }

13.  }

14.}

Figure 2-7: Selecting all books (lambda expressions/extension methods)

01.using System;

02.using System.Linq;

03.  

04.namespace Org.GBarnett.Dns.Linq {

05.  public class Program {

06.    public static void Main() {

07.      Part2DataContext db = new Part2DataContext();

08.      var query = db.Books.Select(x => x);

09.      foreach (var book in query) {

10.        Console.WriteLine("{0} {1}", book.BookId, book.Title);

11.      }

12.    }

13.  }

14.}

I should note that in fig. 2-7 you can omit the Select extension method and you will achieve the same result as when including the Select extension method and its lambda expression parameter; however, the example code given is clearer than when omitting the Select extension method.

Select the titles of the books whose title length is greater than 6 characters

Figure 2-8: Selecting the titles of the books with length > 6 (query)

01.using System;

02.using System.Collections.Generic;

03.using System.Linq;

04.  

05.namespace Org.GBarnett.Dns.Linq {

06.  public class Program {

07.    public static void Main() {

08.      Part2DataContext db = new Part2DataContext();

09.      IEnumerable<string> query = 

10.        from b in db.Books where b.Title.Length > 6 select b.Title;

11.      foreach (string title in query) {

12.        Console.WriteLine("{0}", title);

13.      }

14.    }

15.  }

16.}

Figure 2-9: Selecting the titles of the books with length > 6 (lambda expressions/extension methods)

01.using System;

02.using System.Collections.Generic;

03.using System.Linq;

04.namespace Org.GBarnett.Dns.Linq {

05.  public class Program {

06.    public static void Main() {

07.      Part2DataContext db = new Part2DataContext();

08.      IEnumerable<string> query = 

09.        db.Books.Where(x => x.Title.Length > 6).Select(x => x.Title);

10.      foreach (string title in query) {

11.        Console.WriteLine("{0}", title);

12.      }

13.    }

14.  }

15.}