Paged result with EntityFramework ObjectContext

Paged results are possible using Entity Framework. Sometimes it is desirable to create a utility method to support paged results in a simple way as an extension method. Let's look at how to achieve this. First we download the AdventureWorks 2008 R2 Sales database from: Download AdventureWorks 2008 R2 Sales After downloading the MDF file, restoring it should be easy. Open up SQL Management Studio and create a new query window. Next up, insert the following SQL:

CREATE DATABASE AdventureWorks2008R2  ON (FILENAME = '{drive}:{Folder}') 
FOR ATTACH_REBUILD_LOG

Note that {Drive} here can be c:\ and {Folder} here can be:

 
c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

We should have now our data and we can create a new Console Project in Visual Studio and choose adding an ADO Entity Data Object Model to our project, located under Data. Choose to generate from database and choose the Production.Product table in AdventureWorks2008 database. We can then add an EntityFramework 6.x EntityObject Generator. If you cannot locate this kind of item, download the Extension for Visual Studio from here: EF 6.x EntityObject Generator for C# This is necessary as Microsoft has defaulted to DbContext in EntityFramework 6. To support ObjectContext, we choose to use this extension. Finally we can code our Paged result extension method! Let's define the extension method next:

   public static List<TEntity> PagedResult<TEntity, TKey>(
            this ObjectContext ctx,
            Func<TEntity, TKey> sortingfunc,
            int pageIndex,
            int pageSize = 20)
            where TEntity : EntityObject
        {
            var result = ctx.CreateObjectSet<TEntity>().OrderBy(sortingfunc).Skip(Math.Max(pageIndex - 1, 0) * pageSize).Take(pageSize);
            return result.ToList();
        }

Note that our method did not require that much code. We allow to pass in our ordering member or column of our entity and we choose a default page size of 20 which can be adjusted. We specify the entity type (table) and we specify the page index. You can choose a page index of one to this method and that means the first page. This in fact is technically page index zero and we use the Max method here to protect inputting negative indexes. Let's test this extension method out!

 static void Main(string[] args)
        {

            using (var ctx = new AdventureWorks2008R2Entities())
            {
                var prods = ctx.PagedResult<Product, string>(x => x.Name, 2);


                foreach (var p in prods)
                    Console.WriteLine(p.Name);
            }

            Console.WriteLine("Press any key to continue ..");
            Console.ReadKey();
        }

And we get the desired result:

This may be readily inspected using the AdventureWorks2008 Sales database and select the content from the Production.Product table. So there we are, a reusable method to choose paged contents from a database using Entity Framework and ObjectContext. I prefer ObjectContext instead of DbContext because it provides more lowlevel functionality compared to DbContext. (Although DbContext is more convenient to use).

Create your very own generic GetById method using LINQ Expression Trees

Working with LINQ Expression Trees is sometimes somewhat a tough challenge for C#-programmers, since they usually do not work with Expression trees directly. But LINQ Expression Trees makes it possible to create highly dynamic functions and supports generics quite nicely. Let's create our very own generic GetById method using Linq Expression trees! First off, let's define a simple POCO and use the System.ComponentModel.DataAnnotations.KeyAttribute:

using System.ComponentModel.DataAnnotations;

namespace GetByIdLinqExpression
{
   
    public class ChoccieBar
    {

        [KeyAttribute]
        public int ItemStockId { get; set; }

        public string Title { get; set; }

        public int Qty { get; set; }

        public decimal ItemPrice { get; set; }


    }
}

Okay, let's then define our generic GetById method that will support LINQ expression trees:

using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System;
using System.Collections.Generic;
using System.Linq; 

namespace GetByIdLinqExpression
{
   
    public static class LinqExtensions
    {

        public static TEntity GetById<TEntity>(this IEnumerable<TEntity> source, object primaryKey)
        {
            var itemFound = source.AsQueryable().SingleOrDefault<TEntity>(GetByKeyExpression<TEntity>(primaryKey));
            return itemFound;
        }

        private static Expression<Func<TEntity, bool>> GetByKeyExpression<TEntity>(object primaryKey)
        {
            var primaryKeyProperty = typeof(TEntity).GetProperties()
                .First(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0); 

            //Create entity => portion of lambda expression 
            ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "element");

            //Create entity.Id portion of lambda expression 
            MemberExpression property = Expression.Property(parameter, primaryKeyProperty.Name);

            //Create 'id' portion of lambda expression 
            var equalsTo = Expression.Constant(primaryKey);

            //Create entity.Id == 'id' portion of lambda expression 
            var equality = Expression.Equal(property, equalsTo);

            //finally create the entire expression: entity => entity.Id = 'id' 
            Expression<Func<TEntity, bool>> retVal = Expression.Lambda<Func<TEntity, bool>>(equality,
                new[] { parameter });

            return retVal;
        }

    }

}

Note that in our customized extension method we will first take the IEnumerable collection and use the AsQueryable method, since we will use Linq Expression trees. Next we have our own private method that builds up the necessary lambda expression using parameterexpression, memberexpression, constantexpression and equal or equality expression before finally handing it over to a lambdaexpression. If you find it hard to follow, note that you will get nice tooltips as you debug the method. Learning how to assemble for example lambda expressions in Linq expression trees is a must. And yes, the code can quickly grow. The key point is that once you first got it right, you can unlock the complexity using simple calls such as GetById shown in the unit tests next. And of course other, more complex queries can be built up by simpler ones such as this. And let's then create some unit tests to test out this little baby:

    [TestClass]
    public class UnitTest1
    {
       
        [TestMethod]
        public void GetByIdDoesNotThrow()
        {
            var choccieBars = new List
            {
                new ChoccieBar { ItemStockId = 1, Qty = 5200, Title = "Marsbar", ItemPrice = 2.0M },
                new ChoccieBar { ItemStockId = 2, Qty = 5200, Title = "Milky Way", ItemPrice = 1.75M },
                new ChoccieBar { ItemStockId = 3, Qty = 5200, Title = "Bounty", ItemPrice = 2.25M },
                new ChoccieBar { ItemStockId = 4, Qty = 5200, Title = "Lion", ItemPrice = 1.75M }
            };

            var choccieFound = choccieBars.GetById(4);
            Assert.IsNotNull(choccieFound); 

        }

    }

And the test passes. Happy LINQ Expression coding! Remember that using Linq Expression trees there is a lot of things you can achieve in C#! Only creativity can stop you from what extension methods using linq expression trees can be made! And Linq Expression trees might seem difficult, but get started coding them and you soon will find out that they are fun to work with!

Generic base class supporting ObjectContext CRUD operations

In Entity Framework it is interesting to see if it is possible to create a base class that support CRUD operations with ObjectContext. I want to create a basic scenario where you can either insert, update (insert many) and delete rows into tables. The matching between entities and datacontracts will be matched upon the same kind of name of properties and data type. First off, let us define an interface for such a base class. Note that I have tested the code with Entity Framework 6.1.3.

using System.Collections.Generic;
using System.Data.Entity.Core.Objects.DataClasses;

namespace BaseClassObjectContext
{

    public interface IDefaultDbCrudOperation<TEntity, TDataContract>
        where TEntity : EntityObject
        where TDataContract : class
    {

        List<TDataContract> GetAll();

        TDataContract InsertOrUpdate(TDataContract dataContract);

        bool Delete(TDataContract entity);

        List<TDataContract> InsertOrUpdateMany(List<TDataContract> dataContracts);

    }

}

We will also need the help of Linq Expression trees, so here is a class QueryableExtensions that helps with that:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BaseClassObjectContext
{


    /// <summary>
    /// Enables auto mapping features in Entity Framework
    /// </summary>
    /// <remarks>More information here: http://toreaurstad.blogspot.no/2015/02/automatic-mapping-for-deep-object.html </remarks>
    public static class QueryableExtensions
    {
        public static ProjectionExpression<TSource> Project<TSource>(this IQueryable<TSource> source)
        {
            return new ProjectionExpression<TSource>(source);
        }
    }

    public class ProjectionExpression<TSource>
    {
        private static readonly Dictionary<string, Expression> _expressionCache = new Dictionary<string, Expression>();

        private readonly IQueryable<TSource> _source;

        public ProjectionExpression(IQueryable<TSource> source)
        {
            _source = source;
        }

        public IQueryable<TDest> To<TDest>()
        {
            var queryExpression = GetCachedExpression<TDest>() ?? BuildExpression<TDest>();

            return _source.Select(queryExpression);
        }

        private static Expression<Func<TSource, TDest>> GetCachedExpression<TDest>()
        {
            var key = GetCacheKey<TDest>();

            return _expressionCache.ContainsKey(key) ? _expressionCache[key] as Expression<Func<TSource, TDest>> : null;
        }

        public static Expression<Func<TSource, TDest>> BuildExpression<TDest>()
        {
            var sourceProperties = typeof(TSource).GetProperties();
            var destinationProperties = typeof(TDest).GetProperties().Where(dest => dest.CanWrite);
            var parameterExpression = Expression.Parameter(typeof(TSource), "src");

            var bindings = destinationProperties
                                .Select(destinationProperty => BuildBinding(parameterExpression, destinationProperty, sourceProperties))
                                .Where(binding => binding != null);

            var expression = Expression.Lambda<Func<TSource, TDest>>(Expression.MemberInit(Expression.New(typeof(TDest)), bindings), parameterExpression);

            var key = GetCacheKey<TDest>();

            _expressionCache.Add(key, expression);

            return expression;
        }       

        private static MemberAssignment BuildBinding(Expression parameterExpression, MemberInfo destinationProperty, IEnumerable<PropertyInfo> sourceProperties)
        {
            var sourceProperty = sourceProperties.FirstOrDefault(src => src.Name == destinationProperty.Name);

            if (sourceProperty != null)
            {
                return Expression.Bind(destinationProperty, Expression.Property(parameterExpression, sourceProperty));
            }

            var propertyNameComponents = SplitCamelCase(destinationProperty.Name);

            if (propertyNameComponents.Length >= 2)
            {
                sourceProperty = sourceProperties.FirstOrDefault(src => src.Name == propertyNameComponents[0]);
                if (sourceProperty == null)
                    return null;

                var propertyPath = new List<PropertyInfo> { sourceProperty };
                TraversePropertyPath(propertyPath, propertyNameComponents, sourceProperty);

                if (propertyPath.Count != propertyNameComponents.Length)
                    return null; //must be able to identify the path 

                MemberExpression compoundExpression = null;

                for (int i = 0; i < propertyPath.Count; i++)
                {
                    compoundExpression = i == 0 ? Expression.Property(parameterExpression, propertyPath[0]) :
                        Expression.Property(compoundExpression, propertyPath[i]);
                }

                return compoundExpression != null ? Expression.Bind(destinationProperty, compoundExpression) : null;
            }

            return null;
        }

        private static List<PropertyInfo> TraversePropertyPath(List<PropertyInfo> propertyPath, string[] propertyNames,
            PropertyInfo currentPropertyInfo, int currentDepth = 1)
        {
            if (currentDepth >= propertyNames.Count() || currentPropertyInfo == null)
                return propertyPath; //do not go deeper into the object graph

            PropertyInfo subPropertyInfo = currentPropertyInfo.PropertyType.GetProperties().FirstOrDefault(src => src.Name == propertyNames[currentDepth]);
            if (subPropertyInfo == null)
                return null; //The property to look for was not found at a given depth 

            propertyPath.Add(subPropertyInfo);

            return TraversePropertyPath(propertyPath, propertyNames, subPropertyInfo, ++currentDepth);
        }

        private static string GetCacheKey<TDest>()
        {
            return string.Concat(typeof(TSource).FullName, typeof(TDest).FullName);
        }

        private static string[] SplitCamelCase(string input)
        {
            return Regex.Replace(input, "([A-Z])", " $1", RegexOptions.Compiled).Trim().Split(' ');
        }

    }

}

Next off we need a utility method for reflection:

using System;
using System.ComponentModel;
using System.Linq; 

namespace BaseClassObjectContext
{
    
    public static class ReflectionExtensions
    {


        public static bool HasAttribute<TAttribute>(this PropertyDescriptor pr)
        {
            return pr.Attributes.OfType<TAttribute>().Any(); 
        }

    }
}

The base class for handling all this is then is the following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Core.Objects.DataClasses;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BaseClassObjectContext
{


    public class BaseDataManager<TEntity, TDataContract> : IDefaultDbCrudOperation<TEntity, TDataContract>
        where TEntity : EntityObject
        where TDataContract : class
    {

        public bool Delete(TDataContract dataContract)
        {
            using (var ctx = new BooksEntities())
            {
                var primaryKey = GetPrimaryKey(dataContract);
                var entityFound = FindEntityByKey(primaryKey, ctx);
                if (entityFound == null)
                    return false;
                ctx.CreateObjectSet<TEntity>().DeleteObject(entityFound);
                ctx.SaveChanges();
                return true;
            }
        }

        private TEntity FindEntityByKey(object primaryKey, ObjectContext ctx)
        {
            return ctx.CreateObjectSet<TEntity>().SingleOrDefault(GetEqualityExpression(primaryKey));
        }

        /// <summary>
        /// Builds up an equality expression using LINQ Expression trees
        /// </summary>
        /// <param name="primaryKey"></param>
        /// <remarks>Source: http://dotnetspeak.com/2013/09/use-reflection-and-expression-to-find-an-entity-by-primary-key </remarks>
        private static Expression<Func<TEntity, bool>> GetEqualityExpression(object primaryKey)
        {
            var primaryKeyProperty = typeof(TEntity).GetProperties()
                .First(p => p.GetCustomAttributes(typeof(EdmScalarPropertyAttribute), true)
                .Any(pc => ((EdmScalarPropertyAttribute)pc).EntityKeyProperty));

            //Create entity => portion of lambda expression 
            ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "entity");

            //Create entity.Id portion of lambda expression 
            MemberExpression property = Expression.Property(parameter, primaryKeyProperty.Name);

            //Create 'id' portion of lambda expression 
            var equalsTo = Expression.Constant(primaryKey);

            //Create entity.Id == 'id' portion of lambda expression 
            var equality = Expression.Equal(property, equalsTo);

            //finally create the entire expression: entity => entity.Id = 'id' 
            Expression<Func<TEntity, bool>> retVal = Expression.Lambda<Func<TEntity, bool>>(equality,
                new[] { parameter });

            return retVal;
        }

        private TEntity FindEntityByKeys(object[] primaryKeys, ObjectContext ctx)
        {
            foreach (var keyValues in ctx.CreateObjectSet<TEntity>().Select(t =>
                new { Key = t.EntityKey, Values = t.EntityKey.EntityKeyValues }))
            {
                if (keyValues.Values.ToArray().SequenceEqual(primaryKeys))
                    return ctx.GetObjectByKey(keyValues.Key) as TEntity;
            }
            return null;
        }

        public List<TDataContract> GetAll()
        {
            using (var ctx = new BooksEntities())
            {
                return ctx.CreateObjectSet<TEntity>().Project().To<TDataContract>().ToList();
            }
        }

        public TDataContract InsertOrUpdate(TDataContract dataContract)
        {
            using (var ctx = new BooksEntities())
            {
                var primaryKey = GetPrimaryKey(dataContract);
                var entityToInsertOrUpdate = FindEntityByKey(primaryKey, ctx);
                bool isNew = entityToInsertOrUpdate == null;

                entityToInsertOrUpdate = Queryable.AsQueryable(new[] { dataContract }).Project().To<TEntity>().First();

                if (isNew)
                    ctx.CreateObjectSet<TEntity>().AddObject(entityToInsertOrUpdate);
                else
                {
                    var existingEntity = FindEntityByKey(primaryKey, ctx);
                    ctx.CreateObjectSet<TEntity>().Detach(existingEntity);
                    ctx.CreateObjectSet<TEntity>().Attach(entityToInsertOrUpdate);
                    ctx.ObjectStateManager.ChangeObjectState(entityToInsertOrUpdate, System.Data.Entity.EntityState.Modified);
                }

                ctx.SaveChanges();

                return Queryable.AsQueryable(new[] { entityToInsertOrUpdate }).Project().To<TDataContract>().First();
            }
        }  


        public List<TDataContract> InsertOrUpdateMany(List<TDataContract> dataContracts)
        {
            if (dataContracts == null)
                throw new ArgumentNullException("An empty list was provided!");
            var changesMade = new List<TDataContract>();
            foreach (var dc in dataContracts)
                changesMade.Add(InsertOrUpdate(dc)); //Simple logic in this case 
            return changesMade;
        }

        private object GetPrimaryKey(TDataContract entity)
        {
            PropertyDescriptor pDesc = TypeDescriptor.GetProperties(entity)
                .Cast<PropertyDescriptor>().FirstOrDefault(p => p.HasAttribute<KeyAttribute>());
            if (pDesc == null)
                throw new InvalidOperationException("Provided datacontract must have one column with Key attribute!");

            var primaryKey = pDesc.GetValue(entity);
            return primaryKey;
        }

        private string GetPrimaryKeyName(TDataContract entity)
        {

            var primaryKeyProperty = typeof(TEntity).GetProperties()
              .First(p => p.GetCustomAttributes(typeof(EdmScalarPropertyAttribute), true)
              .Any(pc => ((EdmScalarPropertyAttribute)pc).EntityKeyProperty));
            return primaryKeyProperty.Name;
        }

        private static MetadataWorkspace _metaDataWorkSpace;

        public static string GetTableName(Type type, ObjectContext context)
        {
            if (_metaDataWorkSpace == null)
                _metaDataWorkSpace = context.MetadataWorkspace;

            // Get the part of the model that contains info about the actual CLR types
            var objectItemCollection = ((ObjectItemCollection)_metaDataWorkSpace.GetItemCollection(DataSpace.OSpace));

            // Get the entity type from the model that maps to the CLR type
            var entityType = _metaDataWorkSpace
                    .GetItems<EntityType>(DataSpace.OSpace)
                    .Single(e => objectItemCollection.GetClrType(e) == type);

            // Get the entity set that uses this entity type
            var entitySet = _metaDataWorkSpace
                .GetItems<EntityContainer>(DataSpace.CSpace)
                .Single()
                .EntitySets
                .Single(s => s.ElementType.Name == entityType.Name);

            // Find the mapping between conceptual and storage model for this entity set
            var mapping = _metaDataWorkSpace.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
                    .Single()
                    .EntitySetMappings
                    .Single(s => s.EntitySet == entitySet);

            // Find the storage entity set (table) that the entity is mapped
            var table = mapping
                .EntityTypeMappings.Single()
                .Fragments.Single()
                .StoreEntitySet;

            // Return the table name from the storage entity set
            return (string)table.MetadataProperties["Table"].Value ?? table.Name;
        }
    }

}

The following to compact DAL-layer managers can then be defined in a sample database:

namespace BaseClassObjectContext
{

    public class AuthorManager : BaseDataManager<Author, AuthorDataContract>
    {

    }

}

namespace BaseClassObjectContext
{
    
    public class BookManager : BaseDataManager<Book, BookDataContract>
    {

    }

}

See how little code we need to write to work against a table with Entity Framework now and support easy methods such as GetAll, InsertOrUpdate, InsertOrUpdateMany and Delete? This may seem overly complex, but by developing a sturdy generic code that can handle different mapping scenarios, it would be possible to end up with a Data Access Layer (DAL) that is more easy to maintain than a regular one with the same kind of tedious mapping that you end up with the default path chosen. I have also created some default integration tests:

using System;
using NUnit.Framework;

namespace BaseClassObjectContext.Test
{
  
    [TestFixture]
    public class BookAuthorCrudTests
    {

        [Test]
        public void InsertAuthorAndBookAndDeleteAfterwardsTest()
        {

            var authorManager = new AuthorManager();
            var savedAuthor = authorManager.InsertOrUpdate(new AuthorDataContract
            {
                Name = "Anne B. Ragde",
                Age = 58
            });
            var bookManager = new BookManager();
            var savedBook = bookManager.InsertOrUpdate(new BookDataContract
            {
                PageCount = 302,
                Title = "Eremittkrepsene",
                ISBN = " 9788252560930",
                AuthorId = savedAuthor.AuthorId
            });
            Assert.IsTrue(savedAuthor.AuthorId > 0);
            Assert.IsTrue(savedBook.BookId > 0);

            var authors = authorManager.GetAll();
            var books = authorManager.GetAll();

            CollectionAssert.IsNotEmpty(authors);
            CollectionAssert.IsNotEmpty(books);

            bool bookDeleted = bookManager.Delete(savedBook);
            bool authorDeleted = authorManager.Delete(savedAuthor);

            Assert.IsTrue(authorDeleted);
            Assert.IsTrue(bookDeleted);
        }

        [Test]
        public void InsertAuthorAndBookAndGetAllTest()
        {
            var authorManager = new AuthorManager();
            var savedAuthor = authorManager.InsertOrUpdate(new AuthorDataContract
            {
                Name = "Anne B. Ragde",
                Age = 58              
            });
            var bookManager = new BookManager();
            var savedBook = bookManager.InsertOrUpdate(new BookDataContract
            {
                PageCount = 313,
                Title = "Berlinerpopplene",
                ISBN = " 9788249509584",
                AuthorId = savedAuthor.AuthorId
            });
            Assert.IsTrue(savedAuthor.AuthorId > 0); 
            Assert.IsTrue(savedBook.BookId > 0);

            var authors = authorManager.GetAll();
            var books = authorManager.GetAll();

            CollectionAssert.IsNotEmpty(authors);
            CollectionAssert.IsNotEmpty(books);

            savedBook.Title = "Berlinerpoplene";
            var savedBookAgain = bookManager.InsertOrUpdate(savedBook);
            Assert.AreEqual(savedBook.Title, savedBookAgain.Title); 
        }



    }
}

Note that the code works, but needs further refinement and cleanup to support more scenarios. What would be nice would be to device a way to handle navigation properties, lazy loading and lists within the entities. I have not tested this. Some support for navigation properties should be present with the QueryableExtensions class.

Download sample Visual Studio Solution:

[GenericObjectContextCrudSampleSolution.zip | 14.6 MB | (.Zip) file]
The sample database contains two simple tables: Authors and Books, where a Book has an Author. To test out all the code above, you can download a Zip file below with the source code. I have used Visual Studio 2013. Enjoy and happy Entity Framework coding!