Conclusion On Repository Pattern with Entity Framework

I have written a number of posts on this pattern lately. One thing that I used in some of them is return IQueryable. The idea behind this is to limit the number of Select methods to one in your repository. This goal is achieved by allowing calling code to append where or order by information to the return value of the repository select method. The downside of that is that you expose part of your implementation to calling code. I do not see a giant problem with this, as sometime this is a legitimate approach. Having said that, what if you would like to be more string in your data access code? This is the idea behind this post.

I am taking the approach of returning IEnumerable<T> instead. This would be pretty generic and does not reveal as much about my implementation. The rest of the CUD methods should be almost the same as in previous post. I am making a small modification to allow for deferred as well as immediate save. In an ideal world, you really should inherit from this class, so I am marking it as abstract. Now that I am returning IEnumerable, am I back in the boat of endless proliferation of Select methods? I am going to avoid this by allowing the calling code to pass in where and order by data in a generic way using Expressions. I am also going to add paging parameters as well. Sometimes you need to pass in Include information to allow passing of additional tables. If you do not want to expose that much, feel free to remove that parameter as well. Now that I know what functionality I would like to have, I am going to create an interface for my repository.

using System;

using System.Collections.Generic;

using System.Linq.Expressions;

namespace EFCodeFirstExtensions

{

public interface IEFCodeFirstRepository : IDisposable

{

/// <summary>

/// Select data from database using a where clause

/// </summary>

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

/// <param name=”whereClause”>Where clause / function</param>

/// <param name=”orderBy”>Order by clause</param>

/// <param name=”skip”>Paging implementation = number of records to skip</param>

/// <param name=”top”>Paging implementation = number of records to return</param>

/// <param name=”include”>Navigation properties to include</param>

/// <returns>Items selected</returns>

IEnumerable<TItem> Select<TItem>(

Expression<Func<TItem, bool>> whereClause = null,

OrderByClause<TItem>[] orderBy = null,

int skip = 0,

int top = 0,

string[] include = null)

where TItem : class, new();

/// <summary>

/// Update an item

/// </summary>

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

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

TItem Update<TItem>(TItem item, bool saveImmediately = true) where TItem : class, new();

/// <summary>

/// Delete an item

/// </summary>

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

void Delete<TItem>(TItem item, bool saveImmediately = true) where TItem : class, new();

/// <summary>

/// Insert new item into database

/// </summary>

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

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

TItem Insert<TItem>(TItem item, bool saveImmediately = true) where TItem : class, new();

/// <summary>

/// Save all pending changes

/// </summary>

void Save();

}

}

There are a couple of helper class I am using for sorting, primarily to handle sort direction in an abstract fashion.

using System;

namespace EFCodeFirstExtensions

{

public enum SortDirection

{

Ascending,

Descending

}

}

using System;

using System.Linq.Expressions;

namespace EFCodeFirstExtensions

{

public class OrderByClause<T>

where T : class, new()

{

private OrderByClause()

{

}

public OrderByClause(

Expression<Func<T, object>> orderBy,

SortDirection sortDirection = SortDirection.Ascending)

{

OrderBy = orderBy;

SortDirection = sortDirection;

}

/// <summary>

/// Order by expression

/// </summary>

public Expression<Func<T, object>> OrderBy { get; private set; }

/// <summary>

/// Sort direction

/// </summary>

public SortDirection SortDirection { get; private set; }

}

}

The implementation is very simple from a high level. I am adding a bit of protection to the update and delete methods to handle the case when entity is already in the context. I am doing this via context API for Entry method. The comment that points to this method is in green below

public TItem Update<TItem>(TItem item, bool saveImmediately = true)

where TItem : class, new()

{

DbSet<TItem> set = context.Set<TItem>();

var entry = context.Entry(item);

if (entry != null)

{

// entity is already in memory

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

}

else

{

set.Attach(item);

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

}

if (saveImmediately)

{

context.SaveChanges();

}

return item;

}

The most interesting part is Select method. I handling all parameters in order: where, order by, etc… Since I allow nulls, I am making all parameters optional to allow selection of all rows. Here is full code for my implementation

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data.Entity;

using System.Linq;

using System.Linq.Expressions;

namespace EFCodeFirstExtensions

{

public abstract class EFCodeFirstRepository<TContext> : IDisposable, IEFCodeFirstRepository

where TContext : DbContext, new()

{

private TContext context;

/// <summary>

/// Create new instance of repository

/// </summary>

public EFCodeFirstRepository()

{

context = new TContext();

}

/// <summary>

/// Set connection string

/// </summary>

/// <param name=”connectionStringName”>Connection string name from .config file</param>

public void SetConnectionString(string connectionStringName)

{

context.Database.Connection.ConnectionString =

ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

}

/// <summary>

/// Select data from database using a where clause

/// </summary>

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

/// <param name=”whereClause”>Where clause / function</param>

/// <param name=”orderBy”>Order by clause</param>

/// <param name=”skip”>Paging implementation = number of records to skip</param>

/// <param name=”top”>Paging implementation = number of records to return</param>

/// <param name=”include”>Navigation properties to include</param>

/// <returns>Items selected</returns>

public IEnumerable<TItem> Select<TItem>(

Expression<Func<TItem, bool>> whereClause = null,

OrderByClause<TItem>[] orderBy = null,

int skip = 0,

int top = 0,

string[] include = null)

where TItem : class, new()

{

IQueryable<TItem> data = context.Set<TItem>();

// handle where

if (whereClause != null)

{

data = data.Where(whereClause);

}

//handle order by

if (orderBy != null)

{

int iteration = 0;

orderBy.ToList().ForEach(one =>

{

if (iteration == 0)

{

if (one.SortDirection == SortDirection.Ascending)

{

data = data.OrderBy(one.OrderBy);

}

else

{

data = data.OrderByDescending(one.OrderBy);

}

}

else

{

if (one.SortDirection == SortDirection.Ascending)

{

data = ((IOrderedQueryable<TItem>)data).ThenBy(one.OrderBy);

}

else

{

data = ((IOrderedQueryable<TItem>)data).ThenByDescending(one.OrderBy);

}

}

iteration++;

});

}

// handle paging

if (skip > 0)

{

data = data.Skip(skip);

}

if (top > 0)

{

data = data.Take(top);

}

//handle includes

if (include != null)

{

include.ToList().ForEach(one => data = data.Include(one));

}

foreach (var item in data)

{

yield return item;

}

}

/// <summary>

/// Insert new item into database

/// </summary>

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

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

public TItem Insert<TItem>(TItem item, bool saveImmediately = true)

where TItem : class, new()

{

DbSet<TItem> set = context.Set<TItem>();

set.Add(item);

if (saveImmediately)

{

context.SaveChanges();

}

return item;

}

/// <summary>

/// Update an item

/// </summary>

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

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

public TItem Update<TItem>(TItem item, bool saveImmediately = true)

where TItem : class, new()

{

DbSet<TItem> set = context.Set<TItem>();

var entry = context.Entry(item);

if (entry != null)

{

// entity is already in memory

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

}

else

{

set.Attach(item);

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

}

if (saveImmediately)

{

context.SaveChanges();

}

return item;

}

/// <summary>

/// Delete an item

/// </summary>

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

/// <param name=”saveImmediately”>If set to true, saved occurs right away</param>

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

public void Delete<TItem>(TItem item, bool saveImmediately = true)

where TItem : class, new()

{

DbSet<TItem> set = context.Set<TItem>();

var entry = context.Entry(item);

if (entry != null)

{

// entity is already in memory

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

}

else

{

set.Attach(item);

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

}

if (saveImmediately)

{

context.SaveChanges();

}

}

/// <summary>

/// Save all pending changes

/// </summary>

public void Save()

{

context.SaveChanges();

}

/// <summary>

/// Dispose context

/// </summary>

public void Dispose()

{

if (context != null)

{

context.Dispose();

}

}

}

}

As you can see, there is a variety of ways to implement the pattern, with a number of people usually defending passionately one approach or another. My, I am in a pragmatic camp, trying to get things done. Hence, feel free to use whichever version you like best.

There is one known issue with the code above. You cannot use certain types for order by clause. This is because of the issue with Expression<T, object> with cause issue with Linq trying to case something like int to object. I am going to try to find an answer to that and blog about it.

Email me any questions you might have.

6 Comments

  1. Interesting post. It would be very helpful if you could show an example of calling the Select() method passing an OrderByClause.

    Also, in response to the issue about casing int to object, would this approach of boxing return type to object from http://www.singingeels.com/Articles/Self_Sorting_GridView_with_LINQ_Expression_Trees.aspx help in some way?

    var parameterExpression = new GenericSortHelper().GetExpressionParameter();

    var sortExpression = System.Linq.Expressions.Expression.Lambda<Func>
    (System.Linq.Expressions.Expression.Convert(System.Linq.Expressions.Expression.Property(parameterExpression, sortField), typeof(object)), parameterExpression);

    public class GenericSortHelper
    {
    public ParameterExpression GetExpressionParameter()
    {
    return Expression.Parameter(typeof(T), “item”);
    }
    }

  2. Pingback: Update to EF Code First Extras Project « Sergey Barskiy's Blog

  3. I wrote another blog post where I formalized “my” view on the pattern. In case of joins, you have to inherit from base repository and write a custom method to select the data you need. I would really recommend to also (if you have time) having custom select repository for each use case (single table or a small set of tables) so that you can remove dependency on IQueryable.
    Hope this helps.

Leave a Reply

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