Repository Pattern with Entity Framework

Repository pattern is a popular architecture pattern that is used to create data access code / layer for an application.  Basic principal behind the pattern is that business object in your application never talks to database directly, but instead talks to repository that takes POCO classes and also returns POCO classes as results of a query.  This way your business layer does not take on a dependency on a specific database or data access layer implementation.  As we see from experience, data access technology does not see to be handing around for very long.

As I was working on entity framework code first, I decided to take a few minutes to develop a number of  convenience classes that would make it easier to repositories that internally use entity framework code first.  Typically, you would create a repository for a set of classes, and this repository will be responsible for querying some data as well as CUD operations.  For example, if you had a product table, you might have repository with methods such as CreateProduct(Product product), UpdateProduct(Product product), DeleteProduct(Product product), GetProductsOrderedByName(int pageNumber, int rowsPerPage), GetProductsOrderedByNumber(int pageNumber, int rowsPerPage), etc…  As you develop more of your application, you might end up with a whole number of repositories.  A lot of them might have similar or the same methods.

I would like to simplify this approach by creating a generic repository that works with DbContext.  So, I am going to create a generic repository for  context type.

  /// <summary>

    /// Repository base class used with DbContext

    /// </summary>

    /// <typeparam name="TContext">Type of DdContext that this repositiory operates on</typeparam>

    public class EFRepository<TContext> : IDisposable

        where TContext : DbContext, IObjectContextAdapter, new()

    {

        private TContext context;

 

        private EFRepository()

        {

 

        }

        /// <summary>

        /// Create new instance of repository

        /// </summary>

        /// <param name="connecstionStringName">Connection string name from .config file</param>

        public EFRepository(string connecstionStringName)

        {

            context = new TContext();

            context.Database.Connection.ConnectionString =

                ConfigurationManager.ConnectionStrings[connecstionStringName].ConnectionString;

        }

 

        /// <summary>

        /// Dipose repository

        /// </summary>

        public void Dispose()

        {

            if (context != null)

            {

                context.Dispose();

                context = null;

            }

        }

 

As you can see, I am using DbContext as a parameter.  I also force to user to supply connection string.  In constructor I am creating new instance of the context.  As a good little developer, I am using IDisposable interface to dispose of the context.  So far pretty simple.  Next, I would like to write generic query mechanism via Select method.

 

 

        /// <summary>

        /// Select data from database

        /// </summary>

        /// <typeparam name="TItem">Type of data to select</typeparam>

        /// <returns></returns>

        public IQueryable<TItem> Select<TItem>()

           where TItem : class, new()

        {

            PropertyInfo property = GetDbSet(typeof(TItem));

 

            DbSet<TItem> set = property.GetValue(context, null) as DbSet<TItem>;

 

            return set;

        }

 
        private PropertyInfo GetDbSet(Type itemType)
        {
            var properties = typeof(TContext).GetProperties().

Where(item => item.PropertyType.Equals(
typeof(DbSet
<>).MakeGenericType(itemType)));
 
            return properties.First();
        }

 

My select method returns IQueryable.  This way the user of the repository can supply strongly types where and order by clauses as well as Top() and Skip() methods for paging.  Here is an example:

using (EFRepository<ProductContext> repository = new EFRepository<ProductContext>("ProductConnection"))

            {

 

                var ordered = repository.Select<Product>().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

 

This way I can avoid a whole slew of Select methods and the only dependency I take on is IQueryable, which is a generic interface.in System.Linq namespace located in System.Core.  This to me is a small price to pay for code reduction in repository. 

Next, insert code.  This is super simple of course. 

        /// <summary>

        /// Insert new item into database

        /// </summary>

        /// <typeparam name="TItem">Type of item to insert</typeparam>

        /// <param name="item">Item to insert</param>

        /// <returns>Inserted item</returns>

        public TItem Insert<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Add(item);

            context.SaveChanges();

            return item;

        }

 

Again, I am using a bit of reflection to find the property DbSet property in the context.  Once I find the set, I add new item to it, fire Save and return new item.  I am using Identity field, and conveniently enough returned item will have the value populated.

Here is an update method:

        /// <summary>

        /// Update na item

        /// </summary>

        /// <typeparam name="TItem">Type of item to update</typeparam>

        /// <param name="item">Item to update</param>

        /// <returns>Updated item</returns>

        public TItem Update<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Attach(item);

            context.Entry(item).State = System.Data.EntityState.Modified;

            context.SaveChanges();

            return item;

        }

 

Update code is only slightly more complicated.  I am simply setting the state to modified after I am attaching the item in order to force the update.  Delete statement is about the same:

 

 

 

        /// <summary>

        /// Delete an item

        /// </summary>

        /// <typeparam name="TItem">Type of item to delete</typeparam>

        /// <param name="item">Item to delete</param>

        public void Delete<TItem>(TItem item)

           where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            var entry = context.Entry(item);

            if (entry != null)

            {

                entry.State = System.Data.EntityState.Deleted;

            }

            else

            {

                set.Attach(item);

            }

            context.Entry(item).State = System.Data.EntityState.Deleted;

            context.SaveChanges();

        }

The only difference is that I am testing for entity not being in the context, which is really should not be necessary ordinarily, but I ran into an issue like this in my test project, so I added this code to it.

That is all there is to it.  Here is the full class code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Entity;

using System.Linq.Expressions;

using System.Reflection;

using System.Configuration;

using System.Data.Entity.Infrastructure;

using System.Data.Objects;

 

namespace RepositoryEFCodeFirst

{

    /// <summary>

    /// Repository base class used with DbContext

    /// </summary>

    /// <typeparam name="TContext">Type of DdContext that this repositiory operates on</typeparam>

    public class EFRepository<TContext> : IDisposable

        where TContext : DbContext, IObjectContextAdapter, new()

    {

        private TContext context;

 

        private EFRepository()

        {

 

        }

        /// <summary>

        /// Create new instance of repository

        /// </summary>

        /// <param name="connecstionStringName">Connection string name from .config file</param>

        public EFRepository(string connecstionStringName)

        {

            context = new TContext();

            context.Database.Connection.ConnectionString =

                ConfigurationManager.ConnectionStrings[connecstionStringName].ConnectionString;

        }

 

        /// <summary>

        /// Dipose repository

        /// </summary>

        public void Dispose()

        {

            if (context != null)

            {

                context.Dispose();

                context = null;

            }

        }

 

 

        /// <summary>

        /// Select data from database

        /// </summary>

        /// <typeparam name="TItem">Type of data to select</typeparam>

        /// <returns></returns>

        public IQueryable<TItem> Select<TItem>()

           where TItem : class, new()

        {

            PropertyInfo property = GetDbSet(typeof(TItem));

 

            DbSet<TItem> set = property.GetValue(context, null) as DbSet<TItem>;

 

            return set;

        }

 

        /// <summary>

        /// Insert new item into database

        /// </summary>

        /// <typeparam name="TItem">Type of item to insert</typeparam>

        /// <param name="item">Item to insert</param>

        /// <returns>Inserted item</returns>

        public TItem Insert<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Add(item);

            context.SaveChanges();

            return item;

        }

 

        /// <summary>

        /// Update na item

        /// </summary>

        /// <typeparam name="TItem">Type of item to update</typeparam>

        /// <param name="item">Item to update</param>

        /// <returns>Updated item</returns>

        public TItem Update<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Attach(item);

            context.Entry(item).State = System.Data.EntityState.Modified;

            context.SaveChanges();

            return item;

        }

 

        /// <summary>

        /// Delete an item

        /// </summary>

        /// <typeparam name="TItem">Type of item to delete</typeparam>

        /// <param name="item">Item to delete</param>

        public void Delete<TItem>(TItem item)

           where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            var entry = context.Entry(item);

            if (entry != null)

            {

                entry.State = System.Data.EntityState.Deleted;

            }

            else

            {

                set.Attach(item);

            }

            context.Entry(item).State = System.Data.EntityState.Deleted;

            context.SaveChanges();

        }

 

        private PropertyInfo GetDbSet(Type itemType)

        {

            var properties = typeof(TContext).GetProperties().Where(item => item.PropertyType.Equals(typeof(DbSet<>).MakeGenericType(itemType)));

 

            return properties.First();

        }

 

    }

}

 

Here is sample code that uses this repository:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace RepositoryEFCodeFirst

{

    class Program

    {

        static void Main(string[] args)

        {

            using (EFRepository<ProductContext> repository = new EFRepository<ProductContext>("ProductConnection"))

            {

                Console.WriteLine("Total products in DB : " +

                    repository.Select<Product>().Count().ToString());

 

                Product newProduct = new Product();

                newProduct.ProducName = "Plates";

                newProduct.ProducNumber = "001";

                newProduct.Notes = "SOme notes for plates";

                newProduct.IsActive = true;

 

                newProduct = repository.Insert<Product>(newProduct);

 

                var ordered = repository.Select<Product>().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

 

                Console.WriteLine("New id is " + newProduct.ProductId.ToString());

 

 

                var aProduct = repository.Select<Product>().Where(one => one.IsActive).First();

                aProduct.ProducName = "Updated Plates";

 

                aProduct = repository.Update<Product>(aProduct);

 

                aProduct = repository.Select<Product>().Where(one => one.ProductId == newProduct.ProductId).First();

                Console.WriteLine("Update name is: " + aProduct.ProducName.ToString());

 

                repository.Delete<Product>(aProduct);

 

            }

        }

    }

}

 

I captured the result of the query (in bold) in profiler of my select with where and order by to ensure the operation takes place on the server.  Here is the query.

SELECT

[Extent1].[ProductId] AS [ProductId],

[Extent1].[ProducNumber] AS [ProducNumber],

[Extent1].[ProducName] AS [ProducName],

[Extent1].[Notes] AS [Notes],

[Extent1].[IsActive] AS [IsActive]

FROM [dbo].[Products] AS [Extent1]

WHERE 1 = [Extent1].[IsActive]

ORDER BY [Extent1].[ProducNumber] ASC

This concludes sample repository pattern implementation for Entity Framework Code First.  On a side note, I have not been blogging quite as much.  As I found out, spending 2-3 hours in the car every day going to/from work is not very conducive to blogging Smile.

Thanks.

33 Comments

  1. Pingback: Random Ramblings From An IT Dude

  2. Good idea Sergey. I have a couple questions regarding your implementation:

    – every call to GetDbSet is followed by a bit of reflection to get the property, maybe change GetDbSet(Type itemType) to GetDbSet and return the actual DBSet?
    – more importantly, why are you using reflection to get Set instead of using context.Set()?
    – why aren’t you using type inference in your sample usages
    newProduct = repository.Insert(newProduct);
    could be
    newProduct = repository.Insert(newProduct);

    Thanks

  3. Hi Please let know how I can integrate this with WCF and invoke the operations from the client application which could be Console based, web based or winform based.

    Any help is appreciated.

    Thanks
    Ananda

  4. @Ob
    Please take a look at the other posts I wrote on the subject, further refining the idea and offering more options. I just want to make sure you get the implementation right for you.
    The class is super simple
    using System.Data.Entity;
    using System.Data.Objects;
    using System.Data.Entity.Infrastructure;

    namespace RepositoryEFCodeFirst
    {
    public class ProductContext : DbContext, IObjectContextAdapter
    {
    public DbSet Products { get; set; }

    public ObjectContext UnderlyingContext
    { get { return ((IObjectContextAdapter)this).ObjectContext; } }
    }
    }

  5. Hi,

    I’m trying to implement this with DB first, where my Connection string has metadata attribute related to EF.

    connectionString=”metadata=res://*/EntityData.DemoDB.csdl|res://*/EntityData.DemoDB.ssdl|res://*/EntityData.DemoDB.msl;provider=System.Data.SqlClient;provider connection string="data source=RTS-ALPHASQLEXPRESS;initial catalog=DemoDB;persist security info=True;user id=sa;password=E1505E1505E15;multipleactiveresultsets=True;App=EntityFramework"” providerName=”System.Data.EntityClient”

    I’ve followed all of these things and when I run the code to check it. I get problem with line.

    context.Database.Connection.ConnectionString =
    ConfigurationManager.ConnectionStrings[connecstionStringName].ConnectionString;

    The it doesn’t seem to find connection string from my webconfig if i provide it “name=MyConnectionStr” on the other hand it does find the ConnectionString if I provide it as “MyConnectionStr”. But then it shows an exception.. Keyword not supported: ‘metadata’

    with following stacktrace
    at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
    at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
    at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
    at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
    at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
    at IST.Singularity.Infrastructure.Repository.EFRepository`1..ctor() in E:Singularity YardPrototypeIST.Singularity.WebIST.Singularity.InfrastructureRepositoryEFRepository.cs:line 27
    at IST.Singularity.Domain.TestRepository..ctor() in E:Singularity YardPrototypeIST.Singularity.WebIST.Singularity.DomainTestRepository.cs:line 10
    at IST.Singularity.Web.Controllers.HomeController.Index() in E:Singularity YardPrototypeIST.Singularity.WebIST.Singularity.WebControllersHomeController.cs:line 14
    at lambda_method(Closure , ControllerBase , Object[] )
    at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
    at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
    at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
    at System.Web.Mvc.ControllerActionInvoker.c__DisplayClass15.b__12()
    at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

    any thoughts plz

  6. Hi Sergey,

    Really great artical that explained me Repository pattern in simple way.
    I am implementing this with a simple application. Rather than product i use User class. Connection is set properly but:
    private PropertyInfo GetDbSet(Type itemType)
    {
    var properties = typeof(TContext).GetProperties().Where(item => item.PropertyType.Equals(typeof(DbSet).MakeGenericType(itemType)));
    return properties.First();
    }

    This method gives me error: Sequence contains no elements.
    I checked properties and its count is 0. So could you please help me on this.

    Thanks

    Thanks

  7. Hi Sergey,

    Thank you for the tutorial. I’ve been working with EF for a few months now and finally decided to take on Unit testing with EF implementation. Your tutorial really was direct and exactly what I needed to get started.

    Thank you,

  8. Hello Sergey,
    Pretty clean your repository, I am planning to use it, however I come to a limitation maybe you can help me.

    I want to write a method where I have only a Type as parameter and I want to return back a collection of objects
    IEnumerable GetItems(Type t)

    I implemented so far the call to the Select opertaion like this:
    MethodInfo method = typeof(Repository).GetMethod(“Select”);
    MethodInfo generic = method.MakeGenericMethod(t);
    var result = generic.Invoke(Repository, null);

    however I have no ideea yet how to invoke on the result which is a IQueryable the ToArray() operation. I can’t find the ToArray method if I am looking for it with reflection.

    Thanks for your help.

  9. damn, I figure it out – spent so much time before on it, but finally this will do the job

    Enumerable.ToArray(result);

    The problem with reflection not finding the method is because the ToArray method that I was looking for is an extension method.

  10. Why not just query the DBContext directly? I mean its not really generic in the true sense because you have to know what database you are connecting to, you have to have a predefined DBContext for that database and you have to have predefined POCO classes/entities for that DBContext.

    So considering you need to have all the necessary details to start with, its actually easier to query the context directly and cut out the generics.

    i.e. instead of:

    var ordered = repository.Select().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

    you can just call:

    var ordered = Context.Product.Select().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

    Basically, all the code you have written has allowed you to switch around Product.Select() to .Select()

    Hardly seems worth while to me!

  11. Thank you for sharing, Bob.
    It is true, that if you look at the issue that way, it seems counterproductive. However, I was not trying to replace the DbContext, but instead was sharing my thoughts on creating repository pattern that is using EF Code First. Martin Fowler describes the patter as “Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.” Thus the idea is not refer to EF in your domain object. To elaborate more on the subject, I created a few more posts following this one to help me (and hopefully others) to clarify and solidify the approach in the most pure way possible, I think that having Select method is useful, but deviates from the idea of Repository, which calls for a criteria object to be passed into the repository to do Selects. So, I think if one want to stick to the patter directly, one should have Write repository bases class, then inherit from it for each domain object (or group of objects) and add specific select based methods to that that would return IEnumerable. Just my 2 cents.

  12. Thanks for sharing. I love the way this code is really quite simple and is completely generic. However, I’m not using code first.

    I’m new to some of these concepts. Could you talk a little about what would be needed to use the same code in an application that doesn’t use code first?

  13. @Jonathan Wood
    Are you using Db First approach? Most of the concepts still apply, but you will need to use reflection to manage the API that does not support Set. It should not be hard or much slower.

  14. @Bob
    I need the generic approach because I have a generic functionality (view & controller) that allows me to edit >80 catalogs (nomenclators) in a generic fashion, if I will use operations against the concrete sets, existing in the context, then I will need also concrete implementations for editing each of catalogs.

  15. Hi Sergey,

    I try yo connect this code to an oracle database with odp.net version : 2.102.20 with package OracleV10_2_0_3
    I create an oracle database with only 1 table whose name is EZ_PERIMETER.
    I created the context class :

    public class PerimeterContext : DbContext, IObjectContextAdapter
    {

    public PerimeterContext() : base() { }

    public DbSet Perimeters { get; set; }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Remove();
    modelBuilder.Entity().Property(p => p.GRP_ID).HasColumnName(“GRP_ID”);
    modelBuilder.Entity().Property(p => p.IDENT).HasColumnName(“IDENT”);
    modelBuilder.Entity().Property(p => p.PERIMETER_SOPHIS).HasColumnName(“PERIMETER_SOPHIS”);
    //modelBuilder.Entity().ToTable(“EZ_PERIMETRE”, “EVEREST”);
    }

    public ObjectContext UnderlyingContext
    {
    get { return ((IObjectContextAdapter)this).ObjectContext; } }
    }

    And i created a c# class like this :

    public Perimeter InsertPerimeter(int GroupId,int ident,int sophis)
    {
    using (EFRepository repository = new EFRepository(“EverestDbContext”))
    {
    Perimeter p = new Perimeter();
    p.GRP_ID = 9050;
    p.IDENT = 220930;
    p.PERIMETER_SOPHIS = 2;

    return repository.Insert(p);
    }
    }

    During Insert execution set.Add(item); -> throw an exception

    An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

    –> {“The provider did not return a ProviderManifestToken string.”} for the innerexception, have you got an idea ?

  16. Pingback: Good Generic Repository Pattern when not Using Code First | BlogoSfera

  17. Hello all, its a bit old post anyway a good one. Thank you,
    I am trying to adapt it to my project but I got errors.

    The underlying provider failed on Open. it says.. when attempting insertion. Any idea ?

    also, I got more errors about opening connection. I am using model first, maybe this is the reason, I don’t know, any help will be appreciated Thanks again..

Leave a Reply to Robert Langdon Cancel reply

Your email address will not be published. Required fields are marked *