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!
Moving over to the code bit, I define the following in MainWindow (Source of the form), which is the code behind:
