Published: 25 Apr 2007
By: Granville Barnett

In Part 2 we took a look at LINQ to SQL, how to generate an entity, and also how to query that entity. In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.

Introducing LINQ Series

  • Part 1 In this part you will learn how to the query in-memory collections.
  • Part 2 In this part we will look at querying relational data.
  • Part 3 In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.
  • Part 4 In this part of the series we will use LINQ, ASP.NET and ASP.NET AJAX to replicate the to-do list that Scott Guthrie created a while back to show off the features of ASP.NET AJAX.
  • Part 5 In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.
  • 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

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

    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

    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

    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()

    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

    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

    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

    Summary

    In this article we talked some more about entities, looked at the DataContext (we will revisit this class constantly in future parts), used the designer to generate a method to invoke a sproc and then used our stored procedure in a query. In the next part of this series we will replicate the task manager Scott Guthrie created some time ago when first demonstrating the capabilities of ASP.NET AJAX using LINQ to SQL.

    Introducing LINQ Series

  • Part 1 In this part you will learn how to the query in-memory collections.
  • Part 2 In this part we will look at querying relational data.
  • Part 3 In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.
  • Part 4 In this part of the series we will use LINQ, ASP.NET and ASP.NET AJAX to replicate the to-do list that Scott Guthrie created a while back to show off the features of ASP.NET AJAX.
  • Part 5 In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.
  • <<  Previous Article Continue reading and see our next or previous articles Next Article >>

    About Granville Barnett

    Sorry, no bio is available

    This author has published 32 articles on DotNetSlackers. View other articles or the complete profile here.

    Other articles in this category


    C# 4.0 Reflection Programming - Part 2
    As introduced in the first article, the most typically-used tools associated with .NET reflection ar...
    C# 4.0 Reflection Programming - Part 3
    In the previous article, we used the reflection to obtain the information of an assembly, module, ty...
    C# 4.0 Reflection Programming - Part 4
    In this last article of this series, we will learn what to do with reflection. But before making the...
    Understanding and Using Extension Methods
    Extension methods were new to C# 3.0. They allow you to add a method to an existing type without hav...
    Introduction to C# 3.0 features
    C# 3.0 introduced some of very useful features built on top of 2.0. This article explains the usage,...

    You might also be interested in the following related blog posts


    Oredev Wrap-Up read more
    Introducing SharePoint 2010 Training at U2U read more
    The Underground at PDC read more
    10 resources to learn Moq read more
    Building A Product For Real read more
    My History of Visual Studio (Part 6) read more
    BeginDialOut with Office Communicator Clients read more
    DotNetNuke Fusion Results for Q3 read more
    Dynamic in C# 4.0: Introducing the ExpandoObject read more
    GiveCamps Get a new Sponsor read more
    Top
     
     
     

    Please login to rate or to leave a comment.