ILinqRepository at last

LINQ to SQL is a very easy to use and very powerful tool for many things when dealing with SQL.  But the time has come that I need to wrap it all up and use an iRepository pattern.  I've Googled for the IRepository and found a few useful references but none that will complete my picture.  How ever I did get some good ideas when looking around othe rimplementation of the IRepository pattern that others had implmented.

So why whould I use the iRepository when Linq to Sql and so good?

There are a few time, and this keeps happening that when you come to save an object you forget to use the SubmitChanges(), and what happens if there is a conflict.  You can handle conflicts in Linq to Sql but you need to add extra code for each time you SubmitChanges().  The iRepository I am going to use will wrap all this up and sort it out for us in to one place.  There are a number of reasons, but mainly the iRepository will provide a layer that I can use to hold all these methods in one place.

First the pattern will use Generics.  So this means that you can passing in any Linq to Sql object and it will perform a task.

So here is the Interface I'll be using:

public interface IRepository<T> where T : class
    {
        /// <summary>
        /// Return all instances of type T.
        /// </summary>
        /// <returns></returns>
        IEnumerable<T> All();

        /// <summary>
        /// Return all instances of type T that match the expression exp.
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        IEnumerable<T> FindAll(Func<T, bool> exp);

        /// <summary>Returns the single entity matching the expression. Throws an exception if there is not exactly one such entity.</summary>
        /// <param name="exp"></param><returns></returns>
        T Single(Func<T, bool> exp);

        /// <summary>Returns the first element satisfying the condition.</summary>
        /// <param name="exp"></param><returns></returns>
        T First(Func<T, bool> exp);

        /// <summary>
        /// Mark an entity to be deleted when the context is saved.
        /// </summary>
        /// <param name="entity"></param>
        void MarkForDeletion(T entity);

        /// <summary>
        /// Create a new instance of type T.
        /// </summary>
        /// <returns></returns>
        T CreateInstance();

        /// <summary>Persist the data context.</summary>
        void SaveAll();

        long Count(Expression<Func<T, bool>> criteria);

        void Save(T entity);
    }

So that is the easy part, next is the concreate implementation of the interface

public class Repository<T> : IRepository<T>
        where T : class
    {
        protected DataContext DataContext;

        /// <summary>
        /// Return all instances of type T.
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> All()
        {
            return GetTable;
        }

        /// <summary>
        /// Return all instances of type T that match the expression exp.
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IEnumerable<T> FindAll(Func<T, bool> exp)
        {
            return GetTable.Where(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="exp"></param><returns></returns>
        public T Single(Func<T, bool> exp)
        {
            return GetTable.SingleOrDefault(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="exp"></param><returns></returns>
        public T First(Func<T, bool> exp)
        {
            return GetTable.First(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="entity"></param>
        public virtual void MarkForDeletion(T entity)
        {
            DataContext.GetTable<T>().DeleteOnSubmit(entity);
        }

        /// <summary>
        /// Create a new instance of type T.
        /// </summary>
        /// <returns></returns>
        public virtual T CreateInstance()
        {
            var entity = Activator.CreateInstance<T>();
            GetTable.InsertOnSubmit(entity);
            return entity;
        }

        /// <summary>See _vertexRepository.</summary>
        public void SaveAll()
        {
            DataContext.SubmitChanges();
        }

        public long Count(Expression<Func<T, bool>> exp)
        {
            return GetTable.Where(exp).Count();
        }

        public void Save(T entity)
        {
            Save(new List<T> { entity });
            return;
        }

        public void Save(IEnumerable<T> entities)
        {
            var table = DataContext.GetTable<T>();

            foreach (var entity in entities)
            {
                var entity1 = entity;
                var dbEntity = (from p in table
                                where p == entity1
                                select p).FirstOrDefault();

                if (dbEntity == null)
                    table.InsertOnSubmit(entity);

                try
                {
                    //  Save the changes.
                    DataContext.SubmitChanges();
                }

                //  Detect concurrency conflicts.
                catch (ChangeConflictException)
                {
                    //  Resolve conflicts.
                    DataContext.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
                }
            }
        }


        public Repository(DataContext dataContext)
        {
            DataContext = dataContext;
        }

        #region Properties

        private Table<T> GetTable
        {
            get { return DataContext.GetTable<T>(); }
        }

        #endregion
    }

That is it, job done.

You'll notice that in the Save method it handles quite a lot from inserting a new record, updating an exisiting record if one is found with the addition of an object copier to make life easier and then will save the changes to the database and if there happens to be any conflicts it deals with them.

The Save() will accept either single objects or and IEnumerable of an object making things easier and simpler.

I would love any feed back on what I have produced

To compliment the IRepository concreate class I've built a set of tests using MS Test that create and use a test database:

IRepository.zip (4.11 kb)

IRepository.Tests.zip (47.25 kb)

Transaction scoping within Linq to Sql

If you are like me and us are using an IRepository for Linq to SQL you'll probably at some point need to be able to generate a Transaction.

As the IRepository saves records as it goes and not on a SubmitChanges() we're going to have to use the Microsoft TransactionScope Class, from the System.Transactions (in System.Transactions.dll), which makes a code block transactional.

Here it is:

using (var transaction = new TransactionScope())
{
    try
    {
	//Do something here

        transaction.Complete();
    }
    catch (Exception ex)
    {
        return;
    }
}

It is also worth having a look at Implementing an Implicit Transaction using Transaction Scoping for further information on what you can do.

After playing with this for a while I found that you some times get a MSDTC error, to fixes this take a look at:

Fix MSDTC (Microsoft Distributed transaction coordinator) by Pongsathon Keng

How to generate LINQ to SQL Mapping

You may or may not have come across the ability within LINQ to SQL to have mapping to and from your own objects.

Microsoft has a mapping builder called sqlmetal, which is a code generation tool to generate the mapping automatically.

Here is a sample command line to generate the mapping file.

[code language=c#]

sqlmetal /server:. /database:mydb /map:mydbMappings.xml /code:code.dbml

[/code]

Viewing the SQL that is generated from LINQ to SQL

There comes a time when you are using LINQ to SQL that you just have to find out what SQL is being generated, for what ever reason that is, here a a few ways to get the SQL you are looking for:

You can use SQL Server Profile to see the traffic going to and from the database

But if you are like me you want more control over your processes, so you can use the DataContext.Log, and output the log to a window, or in the case below the console window

One last method is to just write out an objects SQL, using the GetCommand, as seen below

LINQ to Entity Framework

What with Entity Framework around, I have been finding it quite hard to find any good information about the Framework, so I have started to compile a list of good resources

The ADO.NET Entity Framework Overview

Code Samples

Mike Taulty is Mr Entity Framework

Professional ADO.NET 3.5 with LINQ and the Entity Framework

Entity Framework Toolkits & Extras

Transparent Lazy Loading for Entity Framework

LINQ to SQL v LINQ to Entities

Whatare the differences between LINQ to SQL v LINQ to Entities, well:

LINQ to SQL uses tables as entities

LINQ to Entities allows me to write C# LINQ queries that run against a conceptual data model.

The following table provides a summary of the features within each LINQ object

So this means that LINQ to SQL uses a database model to create its entities, where as the Entity Framework work on a layer of abstraction above the data, this is a most important feature.  As most database changes are taken care of by the schema and mapping without requiring a change to the obect model - making it so you do not have to refactor and rebuild your objects.

The second difference is the Entity Framework has the ability to allow the entity inheritance and entity composition.  This means that you can create an entity that is composed of colums originating in multiple tables without any complex join logic.

In summary:

  • If you want the added security of insulation and loose coupling from the underlying database schema to make your object model more resilient to change, use the Entity Framework
  • If you find that you need the features of entity inheritance and entity composition, use the Entity Framework
  • If you already have a large LINQ to SQL codebase that is running just fine without entities, you probably don't need to spend the time to refactor out LINQ to SQL to replace it with LINQ to Entities.
  • If you want to run LINQ queries against an object model, but your object model is a 1:1 mirror of the tables in your database, you probably don't need the Entity Framework.

Using LINQ to SQL

Found these great articles on LINQ to SQL, well worth reading as it the way to go forwards

Using LINQ to SQL (Part 1)

LINQ to SQL (Part 2 - Defining our Data Model Classes)

LINQ to SQL (Part 3 - Querying our Database)

About the author

You have probably figured out by now that my name is Bryan Avery (if not, please refer to your browser's address field).  Technology is more than a career to me - it is both a hobby and a passion.  I'm an ASP.NET/C# Developer at heart...

Month List