Repository pattern and LINQ

Earlier we had a high-level discussion on repositories. Now we are ready to dig in a little deeper. We know that repositories are all about providing a way for our domain logic to access resources in the outside world. We know that they can be used for web services, XML files, and just about anything else. In our case, we will discuss how to access data in a database using the new LINQ to SQL framework.

We haven't really touched upon anything related to the Fisharoo application so far, which means that we don't really have anything in the database for us to play with just yet. For that reason, I created a simple Person table for us to work with. It has the following fields: PersonID, FirstName, LastName, and Email. I then created four entries in the database for us to work with.

CREATE TABLE [dbo].[Person](
   [PersonID] [int] IDENTITY(1,1) NOT NULL,
   [FirstName] [varchar](30) NULL,
   [LastName] [varchar](30) NULL,
   [Email] [varchar](150) NULL
) ON [PRIMARY]

Next, I opened the web.config file and added a database connection to the connectionStrings section.

  <connectionStrings>
    <add name="Fisharoo" connectionString="Server=localhost\\sqlexpress;Initial Catalog=Fisharoo;User=USERNAME;Password=PASSWORD" providerName="System.Data.SqlClient" />
  </connectionStrings>

I then created a data connection in Visual Studio. This is done by opening the Server Explorer window (View | Server Explorer) and right-clicking on the Data Connections node, and then selecting Add Connection.

In the Add Connection window, type in your server name and configure your login credentials (I used SQL Server Authentication).

Once you have all your information entered as you think it should be, click the Test Connection button to check the connections status.

Once your connection reports that it was successful, click OK to continue. Click OK again.

Now expand your new data connection until you see the tables. If you have created a Person table as I had described earlier, you should be able to see it under the Tables node.

Once you have your Person table situated, let's start building our data access layer. Navigate to FisharooCore | Core and create a folder named DataAccess. In that folder, create another folder called Impl (short for Implementation which is a bit wordy!). In your Impl folder, we will create two classes and one dbml file. The first class will be our Connection object (named Connection.cs), and the second file will be called PersonRepository.cs. Once you have those class files created, add a new LINQ to SQL Classes file. Call it Fisharoo.dbml.

With the Fisharoo.dbml file still open, go back into your Data Connections and drag your Person table on to the design surface. Save that file.

Once you save your .dbml file, you will have corresponding classes created for you. You can see them in the Class View (View | Class View).

This takes care of the base framework for some demonstrations. Now, let's dig into those two class files that we have created. If you don't already have them open, open the Connection.cs file and the PersonRepository.cs file.

The Connection class gives us a way to encapsulate our DataContext creation. It has one method that returns the FisharooDataContext object. If we have other DataContexts to work with down the road, they could go here too.

using System.Configuration;
using System.Linq;
using System.Data.Linq;
namespace Fisharoo.FisharooCore.Core.DataAccess.Impl
{
    public class Connection
    {
        public static FisharooDataContext GetContext()
        {
            FisharooDataContext fdc = new FisharooDataContext
               (ConfigurationManager.ConnectionStrings
               ["Fisharoo"].ToString());
            return fdc;
        }
    }
}

There isn't much that is too fancy to explain here. Do you notice that we have references to Linq and Data.Linq? Without these in place, the repository that we are creating won't work. Also note that we are spinning up a new instance of our FisharooDataContext class with a reference to our connection string that we had put in the web.config file earlier.

Now, let's move over to the Person Repository. As the Person table that we created earlier is only for demonstration purposes, so is the repository. Here is that class.

using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
using StructureMap;
namespace Fisharoo.FisharooCore.Core.DataAccess.Impl
{
    [Pluggable("Default")]
    public class PersonRepository : IPersonRepository
    {
        public List<string> GetAllNames()
        {
            List<string> names = new List<string>();
            FisharooDataContext dc = Connection.GetContext();
            var persons = from p in dc.Persons
                          select p;
            foreach (Person p in persons)
            {
                names.Add(p.FirstName + " " + p.LastName);
            }
            return names;
        }
    }
}

Note that it has a StructureMap attribute so that we can swap it out later if we need to. As with all StructureMap classes (and for good design reasons) we have also inherited from an IPersonRepository interface, which clearly defines this class.

There is only one method in our repository now. It is responsible for simply selecting all the people in the Person table and returning a list of the first and the last names of those people. We have a list of type strings for the names collection, which will be returned at the end of the method. We then fetch our DataContext for our query. Once we have the DataContext defined, we can perform our query.

We declare a local variable with the var keyword. This allows us to declare a variable with a dynamic type. In this case, we will be selecting a collection of our Person classes. But we don't really know that for sure! Nor do we really need to know that. Let's look at the query.

var persons = from p in dc.Persons
              select p;

What this says is that we would like to select all the people from the Person table and return a collection of those people. It is very much like SQL. However, the reason we have specified from prior to anything else is so that Visual Studio Intellisense can be used. If we were to select the properties first, Visual Studio wouldn't really help us!

Technically speaking, this query is very much like a SELECT * FROM table type SQL query, which is just as bad in this environment as it is in the SQL environment! As this is only for demo purposes, I wanted to use the simplest query.

Once the query is executed, the persons variable is of type IEnumerable, which will allow us to iterate over it in a loop.

foreach (Person p in persons)
{
    names.Add(p.FirstName + " " + p.LastName);
}

In this loop, we will concatenate the first name and last name of each person and add them to our names collection. Once this is completed, we simply return that collection back to the user of this method.

The demonstration given here is very simple, as I didn't want to overwhelm you. It would get the idea across as to how the Repositories will work in this application, also touching upon the basics of LINQ.