Introducing LINQ – Part 3

Post date: Mar 7, 2011 11:55:27 AM

Introduction

Unlike Part 2 we will use stored procedures to compose our queries, we will then invoke those stored procedures (here on in known as sprocs) via our C# 3.0 code. It goes without saying that this part will once again be littered with code samples!

Entities?

When we talk about entities we are generally talking about a more functional representation of our schema. A perfect view of this is the Visual Studio Orcas LINQ to SQL file designer (Figure 3-1). If you drag a few tables onto the design surface you will see an abstract view of your database schema showing only the entity names and the relationships between the entities.

Figure 3-1: Entities in Visual Studio Orcas

Essentially when we talk about entities in LINQ to SQL we are more or less talking about our applications view of the data - our entities don’t necessarily need to map to tables in our database.

Go ahead and drag a few tables onto the designer.

If you take a look at the code generated for you by the designer you will see several attributes that map the particular class (entity) to a table in the database (Figure 3-2). You will also see that the properties are associated with columns in the classes associated table (Figure 3-3). An interesting thing to note is that there is not a direct mapping between CLR and SQL Server types so these attributes take care of the relevant plumbing to map the types accordingly.

Figure 3-2: Associating a class with a table

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

2.public partial class Book { //...

Figure 3-4: Associating a property with a column in a table

1.[System.Data.Linq.Column(Storage="_BookID", 

2.  Name="BookID", 

3.  DBType="Int NOT NULL IDENTITY", 

4.  IsPrimaryKey=true, 

5.  IsDBGenerated=true, 

6.  CanBeNull=false)]

Inspecting the DataContext class

The DataContext class is the most important class when using LINQ to SQL. If you have inspected the code generated for you by the LINQ to SQL File designer then you will notice that the partial class derives from the System.Data.Linq.DataContext class.

In a nutshell the DataContext is in charge of generating the SQL statement from your language query, and then mapping the rows of data (if any) returned from your database to objects. The DataContext is indeed a very interesting class (we will revisit this class throughout this series!).

If you can’t wait for the future parts of this series then check out this article on getting the changed entities from a DataContext object.

If we construct a simple query (Figure 3-4) we can inspect the SQL that the DataContext generates for us (Figure 3-5).

Figure 3-4: Simple query

01.using System;

02.using System.Collections.Generic;

03.using System.Data.Linq;

04.using System.Linq;

05.  

06.namespace IntroToLinq

07.{

08.  public class Program

09.  {

10.    public static void Main()

11.    {

12.      using (BookShopDataContext db = new BookShopDataContext())

13.      {

14.        IEnumerable<Book> books = from b in db.Books select b;

15.        foreach (Book b in books)

16.        {

17.          Console.WriteLine(b.Title);

18.        }

19.      }

20.    }

21.  }

22.}

Figure 3-5: SQL generated by the DataContext object for Figure 3-4

If a class implements IDisposable then make good use of it!! By wrapping our BookShopDataContext object in a using statement we implicitly call the Dispose() method for this object. Calling Dispose() releases any resources held by our object. If you don’t want to use the using statement call the objects Dispose() method explicitly within a finally block.

I’m not going to cover all of the great things that the DataContext class offers to us – we will do that in subsequent parts. Stay tuned!

Stored Procedures

Let’s take a look at using stored procedures in LINQ to SQL. Up until now we have been composing ad-hoc queries in C#. I like to code my SQL queries as sprocs in the database layer and then invoke those sprocs via my apps DAL – this is a very, very common approach.

First thing we will do is create a simple sproc that simply returns all the names of the publishers, Figure 3-6 shows this.

Figure 3-6: Selecting all the names of the publishers

1.create procedure GetPublishers

2.as

3.select PublisherID, PublisherName

4.from Publishers

5.order by PublisherName

I like to use Microsoft SQL Server Management Studio 2005 to code all my SQL. If you want you can do this in Visual Studio, however, there are some great features in Management Studio like being able to view the execution plan.

If you run this query you will get all the names of the publishers in the database as shown in Figure 3-7.

Figure 3-7: Result of executing sproc defined in Figure 3-6

With our sproc defined in our database we will go back into Visual Studio and drag the stored procedure from the server explorer window onto the designer canvas of the LINQ to SQL File (Figure 3-8).

Figure 3-8: Dragging the GetPublishers sproc onto the design canvas

When you have dragged your sproc onto the canvas you will see that the designer generates a method of the same name. There is a method pane on the designer that allows you to see all methods in your DAL (dragging a sproc onto the designer generates a method that executes your sproc).

Figure 3-9: The method pane

I mentioned in the previous part of this series that we would be using a tool called SQLMetal in this part. I decided not to use that tool for now purely because the designer is a little more educational in that it creates a visual representation of your DAL. In the next part of this series where we create an application using LINQ to SQL we will use the SQLMetal.exe command line tool.

Using our GetPublishers() method

Before we use this method in our code let us first take some time to look at the code that the designer generated for us (Figure 3-10).

Figure 3-10: Generated code for GetPublishers()

01.[global::System.Data.Linq.StoredProcedure(Name="dbo.GetPublishers")]

02.public global::System.Collections.Generic.IEnumerable<GetPublisher> 

03.GetPublishers() 

04.{

05.  global::System.Data.Linq.Provider.IQueryResults<GetPublisher> result = 

06.    this.ExecuteMethodCall<GetPublisher>(this, 

07.    (

08.      (global::System.Reflection.MethodInfo)

09.      (global::System.Reflection.MethodInfo.GetCurrentMethod()))

10.    );

11.    return 

12.    (

13.      (global::System.Collections.Generic.IEnumerable<GetPublisher>)

14.      (result)

15.    );

16.}

The GetPublishers() method is decorated with a StoredProcedure attribute, this attribute associates this method with the appropriated sproc in our database. What we return is an enumeration of type GetPublisher (coincidentally this type looks exactly the same as Publisher – we will rectify this in a moment!). For now we will ignore the reflection stuff and the IQueryResults interface – we will cover those bits in a few parts time!

Before we move on we will use the GetPublishers() method in a query (Figure 3-11).

Figure 3-11: Composing a query using the GetPublishers() method

01.using System;

02.using System.Collections.Generic;

03.using System.Data.Linq;

04.using System.Linq;

05.  

06.namespace IntroToLinq

07.{

08.  public class Program

09.  {

10.    public static void Main()

11.    {

12.      using (BookShopDataContext db = new BookShopDataContext())

13.      {

14.        var publishers = from p in db.GetPublishers() select p;

15.        foreach (GetPublisher publisher in publishers)

16.        {

17.          Console.WriteLine("{0} {1}", 

18.            publisher.PublisherID, 

19.            publisher.PublisherName);

20.        }

21.      }

22.    }

23.  }

24.}

Hang on! What the GetPublishers() method returns is an enumeration of type GetPublisher! What the heck is that?! Good question! Well at the moment the designer is not smart enough to recognize that you are returning back an enumeration of type Publisher, which we already have defined! What we have at the moment are two types exactly the same! We will rectify this now.

1. Go into the BookShop.designer.cs and delete the GetPublisher type.

2. Replace all occurrences of GetPublisher in the GetPublishers method – this includes the method signature (return type), and a few other lines with Publisher.

Once you have completed the above steps your GetPublishers() method should look like that in Figure 3-12.

Figure 3-12: The new, slightly tweaked GetPublishers() method

01.[global::System.Data.Linq.StoredProcedure(Name="dbo.GetPublishers")]

02.public global::System.Collections.Generic.IEnumerable<Publisher> 

03.GetPublishers()

04.{

05.    global::System.Data.Linq.Provider.IQueryResults<Publisher> result =

06.      this.ExecuteMethodCall<Publisher>

07.      (

08.        this,((global::System.Reflection.MethodInfo)

09.        (global::System.Reflection.MethodInfo.GetCurrentMethod()))

10.      );

11.    return ((global::System.Collections.Generic.IEnumerable<Publisher>)

12.    (result));

13.}

You can now modify the code in Figure 3-11 to that shown in Figure 3-13.

Figure 3-13: A more elegantly named return type

01.using System;

02.using System.Collections.Generic;

03.using System.Data.Linq;

04.using System.Linq;

05.  

06.namespace IntroToLinq

07.{

08.  public class Program

09.  {

10.    public static void Main()

11.    {

12.      using (BookShopDataContext db = new BookShopDataContext())

13.      {

14.        var publishers = from p in db.GetPublishers() select p;

15.        foreach (Publisher publisher in publishers)

16.        {

17.          Console.WriteLine("{0} {1}", 

18.            publisher.PublisherID, 

19.            publisher.PublisherName);

20.        }

21.      }

22.    }

23.  }

24.}