Tag Archives : C#


Update: Entity Framework Generic Data Access

Introduction

Hello and apologies for the long delay between articles, I am now returning to the Entity framework.  Following on from the previous article (Entity Validation) I’m back to review the latest edition of the generic data access class.

In the time between my original series of articles concerning disconnected POCO entities and a generic approach in handling them, I’ve refactored the class(es) a fair bit, to make the code a little more pleasant.  If you want to jump straight to the sample solution, I’ll place a link to it here and at the end of the article.

This article was originally published at Sanders Technology.  Check the site for updates and additional technology goodness.

This is a direct update to this former article: Flexible Data Operations.

Re-introducing the Data Access Base Class™

The concept was clear – have a base class which could be extended (by virtue of derived classes) which implements a common interface and provides very obvious functionality, such as Select, Insert, Update and Delete.

Being generic, the generic data access class is designed to work on a per-class basis, but when object graphs are introduced into the mix (courtesy of relationships or navigation properties), we end up having to deal with multiple classes.

Now, to quote an earlier article, here are the design prerogatives yet again:

Generic Implementation Design

I’m not going to go through the class function by function (too time consuming) however I do want to walk through the design decisions I made when considering this approach.

  1. Reusable

    The original intention was to conserve and protect the usage of the EF’s DbContext.  However, I also wanted an ability to encapsulate common queries in classes deriving from the generic implementation.  In the end I came up with the solution presented here.  Chances are high that there’s a more elegant approach, and I’m happy to hear from folks with suggestions.

  2. Generic

    Another key was to try and encapsulate as much of the common ‘CRUD’ functionality as possible without having to do things that were entity or type-specific.  Generally, with the exception of schema details and relationships, the approach to data access should be as uniform as possible, and so it should be with the mechanisms controlling such access.

  3. Flexible

    As always, providing a useful and flexible interface is a design goal.  There’s not much point introducing a repository or interface based design if consumers will write hacky workarounds to do something they need to do.  Hence, the exposure of IQueryable<T> return types.

  4. Extendable

    Chances are you’ll never fully anticipate all needs, and this is certainly true with persistence.  The aim here is that the generic approach can be extended fairly easily to prove realistically any capability that might be required down the track.  For example, a type-specific accessor (repository) could be implemented on top of the generic class to provide execution of stored procedures.

Class Definition

This is where the true complications start to occur – especially for disconnected or detached entities.  The Entity Framework is best used when a Data or Object context can remain instantiated and can subscribe to changes in entities which were created via the context .

Let’s take a look at what functionality is exposed by the generic class:

image
Figure 1 – The Obligatory Class Diagrams

Under the hood

To reduce the repetitiveness, I’ve combined “Insert” and “Update” into a single call (InsertOrUpdate) since the onus is on the calling code to have set the Object State for each entity correctly anyway.  That leaves us with Select, CreateQuery/GetEntities/GetEntity for reads and Delete for deletions. 

This hasn’t changed much since the earlier versions, but what *has* changed is how inserts, updates and deletes are handled by the class, behind the scenes.  The ApplyStateChanges (private) function has been refactored, and a chunk of functionality has been refactored into a separate function called ProcessEntityState.

Why refactor?  This makes the code a little more readable, and reduces the size of the ApplyStateChanges function so that it is responsible for processing.  There were also some changes to the way linked entities were handled in a many-to-one (or one-to-many) collection.

Changes to Entity Processing

Handling Object State

One big change I’ve made is how an entity set is handled when processing multiple entities.  The original implementation assumed that anything not local to the Data Context should be safely Attached, as per the below:

if (!ExistsLocal<T2>(item))
{
    dbSet.Attach(item);
}

if (item.State == ObjectState.Modified)
{
    dbSet.Attach(item);
}

However, after some more detailed testing, the code should respect the Object State which the calling code has applied to the entity (rather than relying solely on the Data Context’s local cache):

if (item.State == ObjectState.Modified)
{
    if (!ExistsLocal<T2>(item))
    {
        dbSet.Attach(item);
    }
}

Bug Fix: CreateQuery

A sample set of data I was using, went over the 100 row mark at one point, when I discovered a little faux par:

/// <summary> /// Allows freeform data queries from outside the Data Access classes,

/// without exposing the data context directly /// </summary> /// <returns>An IQueryable of type T - limited to 100 rows</returns> public IQueryable<T> CreateQuery() { IQueryable<T> _query = DataContext.Set<T>().AsQueryable<T>(); // _query = _query.Take(100); return _query; }

Applying the Take operation at this stage was actually restricting the query to the first 100 rows in the table, not restricting the final query to 100 rows.  Consequently, this was removed!

Finally

Let’s just jump right into the sample solution.  You can download a copy of the example solution

Please email me rob.sanders@sanderstechnology.com if you find any issues, have questions or leave a comment here…


Flexible Data Operations with the Entity Framework V6 RC1

Introduction

Recently I began writing a series of articles demonstrating how you could take a blank Database schema and bring it all together with a fully integrated object relational mapping access model (Entity Framework) and then exposing the entire object graph via WCF web services in a completely detached mode. 

A brief note: Generally, it’s a good idea to check back with the source of this article in case there have been any updates.  I have a habit of finding a few problems from time to time, and need to publish some minor modifications.

Note: the data access code has been updated since this article was first published.  Refer to this article for the latest code.

The last article was titled ‘Basic Data Operations’.  This article also follows on logically from the last article ‘’Solving the detached many-to-many problem with the Entity Framework”.’.

In this article, we’re going to go a lot deeper, and I’m going to introduce some techniques (in C#) for abstracting the EDMX/EF Entity Model away from consuming code but keeping a highly flexible access pattern available for more complex data access scenarios.

If you haven’t read the previous articles, they generally prepare you for the content of this article, but provided you have a sold grounding in the Entity Framework you should be able to follow this article in isolation.

Lastly – no warranty.  I’ve tried my best to anticipate a number of design and environmental factors, but I’ve found the odd issue from time to time.  I don’t warrant that this will necessarily be a solution for your specific scenario(s) and is therefore provided ‘AS-IS’.  I will post updates if I find a problem which can be overcome, so if you have any issues, come back and see if there’s an update or email me directly.

Now that we’ve gotten that out of the way…

The Current Solution

Presently, the solution is still fairly flat, it contains a Class Library assembly which houses the main data access logic, with a Web Service Application consuming it.  There’s two sets of unit test projects providing partial test coverage and a Database project for convenient schema management.

image

 

Data Access

This library contains the Entity Framework model, accompanied by various degrees of data access scaffolding.  The general design of this class has been explained in previous articles, and the focus of this article will be on drilling down further on developments to this project.

There are a number of key classes contained within the library, beginning with the basic POCO classes exposed by the Entity Framework model:

image

These classes are complimented with a series of ‘accessor’ classes which are sort of modelled after the repository pattern.  My main goal with these classes was to provide a flexible and for the better part generic approach to consuming the Entity Framework model.  Some of the approach was influenced by this article.

image

Note that most of the work is performed by the DataAccesor<T> class, of which, type-specific accessors are derived from.  This main class implements a standardised interface so that additional functionality could be added with a different persistence store behind it (although we only support database persistence at the moment).

Each model within the Entity Framework model derives from a base class called EntityBase.  There’s a public property in the base class exposed which is an Enum type called ObjectState.  This property is used by consuming code to manually set the state of each object.

public enum ObjectState
{
    Unchanged = 0,
    Added     = 1,
    Modified  = 2,
    Deleted   = 3,
    Processed = 4
}

The Data Access classes

My main objective was to provide as much flexibility to consuming code without having the Entity Framework’s Data Context object passed around too frequently or too far from the data access assembly.  The aim of the base generic class was to provide full CRUD support whilst restricting how much access the consuming code had to the underlying EF scaffolding.

A Generic Interface

The following interface defines a specific set of functionality which will be exposed by any base data accessor.  This allows for implementation of other abstract providers, assuming we wanted to support other persisted storage options with the same EF model.

image

The Generic Data Access Approach

Each of the above interface functions are implemented within the generic DataAccessor<T> class which derives from a base class (BaseAccessor).  The base class is now responsible for management of the EF Data Context, and implements the IDisposable interface, as well as controls the usage of database transactions.

imageimage

The idea is that individual type-specific classes can derive from the generic base class and provide type-specific queries, pre-defined (and reusable).  The capabilities of the base classes are also available:

image   image

Generic Implementation Design

I’m not going to go through the class function by function (too time consuming) however I do want to walk through the design decisions I made when considering this approach.

  1. Reusable

    The original intention was to conserve and protect the usage of the EF’s DbContext.  However, I also wanted an ability to encapsulate common queries in classes deriving from the generic implementation.  In the end I came up with the solution presented here.  Chances are high that there’s a more elegant approach, and I’m happy to hear from folks with suggestions.

  2. Generic

    Another key was to try and encapsulate as much of the common ‘CRUD’ functionality as possible without having to do things that were entity or type-specific.  Generally, with the exception of schema details and relationships, the approach to data access should be as uniform as possible, and so it should be with the mechanisms controlling such access.

  3. Flexible

    As always, providing a useful and flexible interface is a design goal.  There’s not much point introducing a repository or interface based design if consumers will write hacky workarounds to do something they need to do.  Hence, the exposure of IQueryable<T> return types.

  4. Extendable

    Chances are you’ll never fully anticipate all needs, and this is certainly true with persistence.  The aim here is that the generic approach can be extended fairly easily to prove realistically any capability that might be required down the track.  For example, a type-specific accessor (repository) could be implemented on top of the generic class to provide execution of stored procedures.

Using the Generic Interface – Queries

For flexibility, it’s not necessary to create a concrete class for every model type in the entity model.  The generic DataAccessor<T> class can be constructed directly by specifying the type:

using(DataAccessor<Catalog> acc = new DataAccessor<Catalog>()) { var entity = acc.GetEntity(x => x.CatalogId == 1); Assert.IsNotNull(entity, "There should exist an entity with ID = 1"); var entities = acc.GetEntities(x => x.CatalogId < 10); Assert.IsTrue(entities.Count > 0,
"Should be at least one entity returned"); }

This provides all of the functionality defined in the IDataAccessor interface and more.  This simple example shows how easy it is to query for a single or multiple entities, and makes use of lambda/LINQ expressions to help build the query.

If that’s not enough flexibility for more detailed queries, you can have an IQueryable<T> returned, and from here you can specify almost any combination of filters and inclusions:

using (DataAccessor<Catalog> acc = new DataAccessor<Catalog>()) { var entities = acc.CreateQuery().Where(x => x.CatalogId < 10)
.Include("Genres").ToList();
Assert.IsTrue(entities.Count > 0,
"Should be at least one entity returned"); IQueryable<Catalog> extendedQuery = acc.CreateQuery(); extendedQuery = extendedQuery.Include("Genres"); extendedQuery = extendedQuery.Where(x => !String.IsNullOrEmpty(x.Title)); extendedQuery = extendedQuery.Where(x => x.Genres.Count > 1); entities = extendedQuery.ToList(); //executes query Assert.IsTrue(entities.Count > 0,
"Should be at least one entity returned"); }

Using the Generic Interface – Insert/Modify

We can use the same interface to apply changes to an entity or entities contained within collections (i.e. within the object graph).  As mentioned in the previous article, the client side needs to set the ObjectState property for modified or added entities, for example:

using (CatalogDataAccessor a = new CatalogDataAccessor())
{
    string originalDescription = String.Empty;

    IQueryable<Catalog> query = a.CreateQuery();
    query = query.Take(5);                                
    query = query.Include("Sizes");
    var result = query.ToList();
                
    Assert.IsTrue(result.Count > 2, "Should find at least 2 results");
    Catalog item = result[1];
    Assert.IsTrue(item.Sizes.Count > 0, "Should be at least one size");
    Size editItem = item.Sizes.First();
                
    originalDescription = editItem.Description;
    editItem.Description = "Updated By Unit Test";
    editItem.State = ObjectState.Modified;

    a.InsertOrUpdate(result.ToArray());
    a.SaveChanges();

    editItem.Description = originalDescription;

    a.InsertOrUpdate(result.ToArray());
    a.SaveChanges();
}

 

Using the Generic Interface – Delete

We can also use the data access interface to remove entities (in reality, one or more rows from a database table), but as noted earlier, the client can set the entity state to Deleted (although the implementation sets the status to deleted anyway).

using (SizeDataAccessor a = new SizeDataAccessor())
{
    Size obj = new Size();
    obj.Height = 0;
    obj.Width = 0;
    obj.SizeId = 100; //out of the standard range
    obj.IsCustom = false;
    obj.Description = "Unit Test";
    obj.Dimensions = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
    obj.State = ObjectState.Added;
    a.InsertOrUpdate(obj);
    a.SaveChanges();
}
using (SizeDataAccessor a = new SizeDataAccessor())
{
    var verify = a.GetEntity(x => x.SizeId == 100);
    Assert.IsNotNull(verify, "Should be saved to the database");

    a.Delete(verify);
    a.SaveChanges();

    verify = a.GetEntity(x => x.SizeId == 100);
    Assert.IsNull(verify, "Should be removed from the database");
}

Managing Entity Relationships

The main benefit of an ORM comes from relationships between entities, but it’s only useful if you can manipulate them succinctly.  Returning the full object graph is useful, but being able to manipulate the structure is better. 

Here’s how we can do this with disconnected POCO objects.

Adding or Updating Many-To-Many Relationships

Mainly the support for adding or updating related entities is as close to the experience you might find with connected entities, with one key exception: the client is responsible for setting the object state of each entity being modified.

Here’s an example of updating a related, many-to-many entity after retrieving a base entity.  Note that in this particular example, since the entities are not subsequently re-queried, the ObjectState changes are not entirely necessary, but included for brevity.

[TestMethod]
public void UpdateManyToMany()
{
    Catalog existing = null;
    Genre other = null;
    String existingValue = String.Empty;
    String existingOtherValue = String.Empty;

    using (CatalogDataAccessor a = new CatalogDataAccessor())
    {
        //Note that we include the navigation property in the query
        existing = a.CreateQuery().Include("Genres").FirstOrDefault();
        Assert.IsTrue(existing.Genres.Count() > 1,
                                     "Should be at least 1 linked item"); } //save the original description existingValue = existing.Description; //set a new dummy value (with a date/time so we can see it working) existing.Description = "Edit " +
                             DateTime.Now.ToString("yyyyMMdd hh:mm:ss"); existing.State = ObjectState.Modified; other = existing.Genres.First(); //save the original value existingOtherValue = other.Description; //set a new value other.Description = "Edit " +
                        DateTime.Now.ToString("yyyyMMdd hh:mm:ss"); other.State = ObjectState.Modified; //a new data access class (new DbContext) using (CatalogDataAccessor b = new CatalogDataAccessor()) { //single method to handle inserts and updates b.InsertOrUpdate(existing); } //return the values to the original ones existing.Description = existingValue; other.Description = existingOtherValue; existing.State = ObjectState.Modified; other.State = ObjectState.Modified; using (CatalogDataAccessor c = new CatalogDataAccessor()) { //update the entities back to normal c.InsertOrUpdate(existing); } }

Adding and Removing FK-based Relationships

The intention here is that a one-to-many relationship results in a single entity on one side of the join (or relationship) and you can set this as you would normally with the Entity Framework, by assigning the ID or entity to the target entity.

To remove the relationship you do the same thing, set the relationship property to NULL.

[TestMethod]
public void CreateRemoveFK()
{            
    File newFile = CreateFile();
    Size newSize = CreateSize();
    using(FileDataAccessor a = new FileDataAccessor())
    {
        a.InsertOrUpdate(newFile);
        a.SaveChanges();
        Assert.IsNotNull(newFile);

        newFile.Size = newSize;
        newFile.SizeId = newSize.SizeId;
        newFile.State = ObjectState.Modified;
        
        a.InsertOrUpdate(newFile);
        a.SaveChanges();
    }
    using (FileDataAccessor b = new FileDataAccessor())
    {
        File result = b.GetEntity(x => x.FileId == newFile.FileId, 
                                  x => x.Size); Assert.IsNotNull(result); Assert.IsNotNull(result.Size); newFile = result; result.Size = null; result.SizeId = null; result.State = ObjectState.Modified; b.InsertOrUpdate(result); b.SaveChanges(); } using (FileDataAccessor c = new FileDataAccessor()) { c.Delete(newSize); c.Delete(newFile); c.SaveChanges(); } }

Note that I’ve added the FK relationship but not to both sides of the relationship in this example.  It seems to work fine if you do or don’t.

Removing Many-To-Many Relationships

Removing a related entity is not as easy as it should be.  Because many-to-many relationships make use of a “join table”, it’s not as simple as setting the relationship to NULL, as it is in the above example.  Instead you have to explicitly ‘delete’ the relationship and you have to specify the navigation property it applies to.

public void AddRemoveRelationship() { Catalog existing = null; using (DataAccessor<Catalog> a = new DataAccessor<Catalog>()) { existing = a.CreateQuery().Include("Genres").FirstOrDefault(); Assert.IsNotNull(existing, "Should find at least one Catalog"); } Genre newEntity = new Genre(); newEntity.State = ObjectState.Added; newEntity.Title = "Unit"; newEntity.Description = "Test"; newEntity.GenreId = 1000; existing.Genres.Add(newEntity); using (DataAccessor<Catalog> a = new DataAccessor<Catalog>()) { a.InsertOrUpdate(existing); a.SaveChanges(); } newEntity.State = ObjectState.Unchanged; existing.State = ObjectState.Modified; using (DataAccessor<Catalog> b = new DataAccessor<Catalog>()) { b.ModifyRelatedEntities<Genre>(existing, x => x.Genres,
EntityState.Deleted, newEntity); b.SaveChanges(); } using (DataAccessor<Genre> c = new DataAccessor<Genre>()) { c.Delete(newEntity); c.SaveChanges(); } }

Therefore, I’ve implemented a function called ‘ModifyRelatedEntities’ which allows for the state to be explicitly set.  Note that you don’t need to call this to create the relationship, only to remove it.

Polymorphism

Finally, before we close out this article, I want to revisit an aspect of some of the previous articles.  When I was building a generic data access class, alongside the type/entity specific ones, it dawned on me that generic considerations should not be side-by-side, but should underpin ‘extended’ data access.

This brought one “minor” complication – generic base classes are hellish in terms of things like copy constructors, since you can’t cast without explicit types.  What I ended up having to do was have the generic implementation derive from another base class, which allows for reuse of an underlying Data Context.

An example:

using(CatalogDataAccessor a = new CatalogDataAccessor())
{
  var result = a.CreateQuery().Where(x => x.CatalogId < 10).ToList(); SizeDataAccessor sa = new SizeDataAccessor(a); var more = sa.CreateQuery().Where(x => x.SizeId < 10).ToList();
}

This is accomplished by the SizeDataAccessor having the following constructor defined:

public SizeDataAccessor(BaseAccessor existing) : 
       base(existing.DataContext) { }

..and the generic data accessor defines a constructor like so:

public DataAccessor(BaseAccessor existing)
{
    DataContext = existing.DataContext;
    Transaction = existing.Transaction;
}

However, this also allows the use of transactions:

using(CatalogDataAccessor a = new CatalogDataAccessor())
{
    a.BeginTransaction();
    var result = a.CreateQuery().Where(x => x.CatalogId < 10).ToList();
    SizeDataAccessor sa = new SizeDataAccessor(a);
    var more = sa.CreateQuery().Where(x => x.SizeId < 10).ToList();
    a.CommitTransaction(); }

 

Summary

This is not the end.  Rather than writing more about the implementation, I’d prefer to upload this article and get the solution sample out as well and let you experiment with the concepts presented here (if you choose to do so).  I’ll write a follow up article examining specific scenarios shortly, and if there are any updates I’ll repost in this article.

This has been a long journey thus far, and there is still plenty to cover off.  The unit tests cover come in at around 75% code coverage, but a lot of the functionality needs to be tested through a WCF interface.  I’ve sort of simulated some of the scenarios by forcing entities to detached states (by disposing the original Data Context) but it’s far from full proof.

Notes about the Solution

You’ll need to download the NuGet package for the Entity Framework v6 RC as I haven’t included it in the archive. The fastest way is to delete the packages.config file in one of the projects, and then install the EF NuGet package.

Feel free to E-mail me rob.sanders@sanderstechnology.com if you have feedback or have questions, or leave a comment on this article.

Solution [ Files ]

Check back for the next article shortly.