So, I am going to talk about LINQ.  There are a lot of great resources out there on LINQ.  I am going to have a series of articles on building an application utilizing LINQ, WCF and other technologies. Some of the things that I want to show is how you can use LINQ as part of your testing.  So lets get started.Customer Definition

So the first thing that we need is some data to define. The sample application that we are going to build is a simple home inventory system.  The image to the right defines a Customer. 

There is three ways that we can generate this data definition.

  1. (SqlMetal.exe)
  2. Designer Wizard (.dbml)
  3. Manually (.cs)

The second option is the simplest but you don't have very much control of what gets generated. So, I favor creating my files manually, because I have total control and I only define what I need (Option 3).

Step 1: Define data class.

Make sure that you have a reference to System.Data.Linq.

Customer Definition(partial)   
    [Table(Name="Customer")]
    public class Customer
    {
        [Column(Name="Id",
         IsDbGenerated=true,
         DbType="int",
         IsPrimaryKey=true)]
        public int Id
        {
            get;
            set;
        }
        
        [Column(Name="FirstName")]
        public string FirstName
        {
            get;
            set;
        }
    }
 To the left is my definition of my Customer table.  What we essentially doing is marking up a class, that LINQ will use to communicate to our database.  This class communicates several key pieces of information to us.  The data table, fields and keys that we define as important.

So starting at the top, we have defined that our Customer class will talk to the Customer table.  This could be a view or a stored procedure.  The [Table(Name="Customer")] attribute handles this for us.  We can do other advanced features, but we will talk about those at a different time.

Next, notice each property is marked with a [Column(Name=?)] column attribute.  This allows LINQ to map your database column with your physical class properties. This allows the flexibility to have different database column names than what you want to expose to your code.  There are additional attributes that allow you to set the Database type, and constraint information.

Last, you will notice that the Id field has some of those additional properties. We are telling LINQ that the Id field is an identity field, or SqlType "int" and is a primary key.  If you want LINQ to handle some of your data input automatically, you have to be verbose on your Column Attribute definitions to handle your constraints and keys.  If this case, since we setup keys on our tables and we want to use those keys to get data.  We have to make sure that LINQ knows that and we will use this in the future.

Step 2: Create Data Context

In this step we are going to create a data context to handle our database interactions.  First, we need to create a class that derives from System.Data.Linq.DataContext.   Second, we create a static Create method to handle the initialization of the class for ease of use.  Third, define tables, procedures and queries.  Below show a simple class for our use.

    public class InventoryDBDataContext : DataContext
    {
        public InventoryDBDataContext(string connection)
            : base(connection)
        {
        }

        public static InventoryDBDataContext Create()
        {
            string connection = ConfigurationManager.ConnectionStrings["InventoryDB"].ConnectionString;
            return new InventoryDBDataContext(connection);
        }

        public Table<Customer> Customers;
    }

You will notice that the static Create method allows for the hiding of implementation.  In the next part you will see how this is helpful.

Step 3: Get Data

Well, we finally are ready to get data.  To get a list of customer, all that we have to do is the following:

using (InventoryDBDataContext context = InventoryDBDataContext.Create())
{
         var customers = (from c in context.Customers
                                 select c).ToList();
         Assert.IsTrue(customers.Count > 0);
}

Now you see that we are now containing the calls with a "using" statement. This allows for better garbage collection.  With the 3.0 framework, you can allow a runtime definition of variables by just using the "var" keyword.  The selection of the data looks very familiar with TSQL statements.  The selection pieces defined:

TSQL Query Definition
from c in context.Customers Search all customers from the Database.
select c return the selected customers as a whole Customer object.
.ToList() Immediately execute the LINQ query.

 

We have now completed the basics with getting started with LINQ.  Next time we will take a deeper look into LINQ queries and other customization  of our classes.

Let me know what you think.  Next time we will cover how to use XML LINQ in unit testing our code.

David Waddleton

Links: