Data Access with the Entity Framework V6 RC1


Introduction

Today I’m following on from my previous series of articles from last week, particularly (and logically) from the article More with the Entity Framework V6 RC1.

This time around, I’m going to go under the covers and document a new approach I’m trying out around the Entity Framework Data Context (DbContext).  Everything you do with the Entity Framework in some way or shape derives from an active DbContext at some point, which means that this object is very powerful.

A word up front: I’m still experimenting with various approaches, so some of the screens or code here may change as I progress through to newer articles.

What is an Entity Framework DbContext?

The DbContext class represents the data access control mechanism through which the Entity Framework completes tasks like creating, querying, updating and deleting schema objects – amongst other things.  Your entity model will have one, and this is the context you’ll need to instantiate to query or manipulate data.

The full capability of the DbContext will be ultimately defined by the level of access the identity (user account) used by the application has – but from a programmatic point of view, the DbContext can perform nearly all obvious data access responsibilities.

Now, the important aspect of the DbContext is that when it is used, it will create an active connection to the persistence store – here are some example screenshots of some basic code showing when the underlying credentials are used to establish a session within SQL Server:

image image
Pre-initialization / DbContext-derived Object is created

image image
First query is performed / Object is disposed

Notice that the ‘Audit Logout’ event isn’t triggered until the DbContext object has been disposed.  The lesson to take away here is that usage of the context is quite key to performance, you need to be very careful with how the context is handled – and disposed of.

Change Tracking

The DbContext is more than just an interface through which you can read and manipulate data and schema objects – it also can track changes in objects.  Therefore, it is important to consider how you use a DbContext, particularly if you wish to add/update or delete data, and specifically if you’re interested in bulk operations.

We’ll cover this off in more detail in the next article.

Limiting Use of the DbContext

Consequently, my initial design goal is to carefully control how the DbContext is used.  My preference has been to abstract the context behind a series of “providers” which expose a strongly typed/defined interface of functions. 

This is, in theory, an approach which can work, but it’s hardly very flexible.  I’ve implemented this approach a few years ago, but there were still too many ways for people to misuse the DbContext.  Now, it’s not possible to completely hide the DbContext, but you can change the access modifier for the Entity Model (DbContext) from public to internal.

You can do this by opening the .EDMX model int he designer, then in the properties window, select the ConceptualEntityModel and change the Entity Container Access property like so:

image

The model context will now only be accessible by classes within the parent assembly.

Structuring Data Access Classes

The key for me, personally, was to figure out how to minimise the number of instances of the Data Context but whilst allowing multiple instances – i.e. avoiding a singleton pattern.  Rightly or wrongly, I’ve structured “data access” classes with the following structure, remembering from the last article that data entity classes derive from the same base class (EntityBase):

image

This design has a base class which is inherited by several context-specific classes.  The base class doesn’t implement the IDataAccessor interface (because it’s sort of generic), but the child classes do.  This allows for each entity-specific accessor to implement entity-specific logic (particularly validation and so on) as well as implementing an consistent set of functionality.

Here, the children classes do not instantiate a Data Context directly, rather the base class does.  This allows for construction of data access either from the base class, or from a context specific class, and the classes can pass around (share) the same context.

The base class also implements IDisposable, and disposes of the Data Context as soon as the object is garbage collected.  this allows us to wrap the class (and classes deriving from the base) with using statements.

public abstract class DataAccessor : IDisposable 

{

#region Private Members internal RSPhotographyEntities _context;

internal RSPhotographyEntities DataContext { get { return _context; } set { _context = value; } }

#endregion
#region Constructors public DataAccessor() { _context = new RSPhotographyEntities(); _context.Configuration.LazyLoadingEnabled = false; _context.Configuration.ProxyCreationEnabled = false; } internal DataAccessor(RSPhotographyEntities access) { _context = access; } #endregion
#region Implements IDispose public void Dispose() { if (_context != null) { _context.Dispose(); _context = null; } } #endregion
}

public class CatalogDataAccessor : DataAccessor, IDataAccessor<Catalog> { #region Constructors public CatalogDataAccessor() : base() { } public CatalogDataAccessor(DataAccessor accessor)

: base(accessor.DataContext) { } internal CatalogDataAccessor(RSPhotographyEntities context)

: base(context) { } #endregion
}

That doesn’t leave us much in the base class, except for supporting functionality – but most essentially, control of the data context.  The only place in the whole assembly which creates a new instance of the DbContext is the constructor of the DataAccessor base class.

image

When we want to use different data accessor classes within the scope of a task or measured unit of work, we can reuse the context by passing the existing data access object to a new instance of another data accessor, like so:

using (CatalogDataAccessor access = new CatalogDataAccessor())
{
    Location loc = new Location();
    loc.LocationId = 1;
    var result = access.FindItemsByLocation(loc);
    Assert.IsTrue(result.Count > 0, "Should be at least one record");
    FileDataAccessor a = new FileDataAccessor(access);
    //access the File Data Accessor with the same context
}

Be careful when reusing data access classes!

With this approach, you only need to ensure the first data access class is disposed for the base (shared) data context to be disposed.  Therefore the following is unnecessary:

[TestMethod] public void ObjectTests() { //new DbContext from the base class using (CatalogDataAccessor a = new CatalogDataAccessor()) { var result = a.CreateQuery<Catalog>().Where(x => x.CatalogId < 10)
.ToList();
//reuses the DbContext

using (SizeDataAccessor sa = new SizeDataAccessor(a)) { var more = sa.CreateQuery<Size>().Where(x => x.SizeId < 10)
.ToList(); } //the following line will throw an exception because the
//DBContext has been disposed
var extra = a.CreateQuery<File>().Where(x => x.FileId < 100)
.ToList(); Assert.IsTrue(extra.Count > 0, "Should be at least one match"); } }

Therefore, you don’t need the second using statement.  If you wanted to be super careful, you could actually take this approach:

[TestMethod] public void ObjectTestsSuccess() { CatalogDataAccessor a = new CatalogDataAccessor(); SizeDataAccessor sa = new SizeDataAccessor(a); try { var result = a.CreateQuery<Catalog>().Where(x => x.CatalogId < 10)
.ToList(); var more = sa.CreateQuery<Size>().Where(x => x.SizeId < 10)
.ToList(); // this should all be fine var extra = a.CreateQuery<File>().Where(x => x.FileId < 100)
.ToList(); Assert.IsTrue(extra.Count > 0, "Should be at least one match"); } finally { a.Dispose(); sa.Dispose(); } }

 

Exposing Queries

So from here you won’t be able to execute a query directly from outside the parent assembly against the Data Model/DbContext directly, but that doesn’t mean the death of freeform queries.  I’ve been working on an approach which will make a query context widely available.

Using the namespace System.Linq, which you need to include wherever you want to do a Linq based query, I’ve written a design which exposes types to direct queries without direct access to the DbContext.  The magic happens in the base DataAccessor class and is exposed through any of the classes inheriting from this base class.

public IQueryable<T> CreateQuery<T>() where T : EntityBase { IQueryable<T> _query = _context.Set<T>().AsQueryable<T>(); _query = _query.Take(100); return _query; }

Note that with this approach, you can implement mandatory restrictions, for example in the above code there’s a limit on the number of rows which can be returned (Take(100)) which limits any query to returning 100 rows.  Here’s an example of how this approach can be used outside the Data Access assembly:

using (CatalogDataAccessor a = new CatalogDataAccessor())
{
    IQueryable<Catalog> query = a.CreateQuery<Catalog>();                
    query = query.Where(x => x.CatalogId < 10);
    query = query.Where(x => x.Title.Contains("s"));                
    var result = query.ToList();
}

Noting that the query will not execute until there is a verb action applied, such as “ToList()”.  Running the above code and viewing with the SQL Profiler produces the following results:

image
Note that until the .ToList() function is called, there’s been no query execution.  Once we step over this call, you can see the exact T-SQL which is generated:

image

Extending Queries

The full range of query functionality is pretty much available, including populating the entire object graph.  You can use this interface to make more extended queries, for example the below:

using (CatalogDataAccessor a = new CatalogDataAccessor())
{
    IQueryable<Catalog> query = a.CreateQuery<Catalog>();
    query = query.Where(x => x.CatalogId < 10);
    query = query.Where(x => x.Title.Contains("s"));
    query = query.Include("Locations");
    query = query.Include("Sizes");
    var result = query.ToList();
    Assert.IsTrue(result.Count > 0, "Should be at least one match");
}

Which produces the following T-SQL when executed:

SELECT
    [UnionAll1].[CatalogId] AS [C1],
    [UnionAll1].[CatalogId1] AS [C2],
    [UnionAll1].[Title] AS [C3],
    [UnionAll1].[Description] AS [C4],
    [UnionAll1].[DateTaken] AS [C5],
    [UnionAll1].[OriginalFilename] AS [C6],
    [UnionAll1].[C1] AS [C7],
    [UnionAll1].[LocationId] AS [C8],
    [UnionAll1].[RegionId] AS [C9],
    [UnionAll1].[Title1] AS [C10],
    [UnionAll1].[Description1] AS [C11],
    [UnionAll1].[C2] AS [C12],
    [UnionAll1].[C3] AS [C13],
    [UnionAll1].[C4] AS [C14],
    [UnionAll1].[C5] AS [C15],
    [UnionAll1].[C6] AS [C16],
    [UnionAll1].[C7] AS [C17]
    FROM  (SELECT
        CASE WHEN ([Join1].[CatalogId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
        [Limit1].[CatalogId] AS [CatalogId],
        [Limit1].[CatalogId] AS [CatalogId1],
        [Limit1].[Title] AS [Title],
        [Limit1].[Description] AS [Description],
        [Limit1].[DateTaken] AS [DateTaken],
        [Limit1].[OriginalFilename] AS [OriginalFilename],
        [Join1].[LocationId1] AS [LocationId],
        [Join1].[RegionId] AS [RegionId],
        [Join1].[Title] AS [Title1],
        [Join1].[Description] AS [Description1],
        CAST(NULL AS int) AS [C2],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS int) AS [C6],
        CAST(NULL AS bit) AS [C7]
        FROM   (SELECT TOP (100) [c].[CatalogId] AS [CatalogId], [c].[Title] AS [Title], [c].[Description] AS [Description], [c].[DateTaken] AS [DateTaken], [c].[OriginalFilename] AS [OriginalFilename]
            FROM [dbo].[Catalog] AS [c] ) AS [Limit1]
        LEFT OUTER JOIN  (SELECT [Extent2].[CatalogId] AS [CatalogId], [Extent3].[LocationId] AS [LocationId1], [Extent3].[RegionId] AS [RegionId], [Extent3].[Title] AS [Title], [Extent3].[Description] AS [Description]
            FROM  [dbo].[CatalogLocation] AS [Extent2]
            INNER JOIN [dbo].[Location] AS [Extent3] ON [Extent3].[LocationId] = [Extent2].[LocationId] ) AS [Join1] ON [Limit1].[CatalogId] = [Join1].[CatalogId]
        WHERE ([Limit1].[CatalogId] < 10) AND ([Limit1].[Title] LIKE N’%s%’)
    UNION ALL
        SELECT
        2 AS [C1],
        [Limit2].[CatalogId] AS [CatalogId],
        [Limit2].[CatalogId] AS [CatalogId1],
        [Limit2].[Title] AS [Title],
        [Limit2].[Description] AS [Description],
        [Limit2].[DateTaken] AS [DateTaken],
        [Limit2].[OriginalFilename] AS [OriginalFilename],
        CAST(NULL AS int) AS [C2],
        CAST(NULL AS int) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS varchar(1)) AS [C5],
        [Join3].[SizeId1] AS [SizeId],
        [Join3].[Description] AS [Description1],
        [Join3].[Dimensions] AS [Dimensions],
        [Join3].[Width] AS [Width],
        [Join3].[Height] AS [Height],
        [Join3].[IsCustom] AS [IsCustom]
        FROM   (SELECT TOP (100) [c].[CatalogId] AS [CatalogId], [c].[Title] AS [Title], [c].[Description] AS [Description], [c].[DateTaken] AS [DateTaken], [c].[OriginalFilename] AS [OriginalFilename]
            FROM [dbo].[Catalog] AS [c] ) AS [Limit2]
        INNER JOIN  (SELECT [Extent5].[CatalogId] AS [CatalogId], [Extent6].[SizeId] AS [SizeId1], [Extent6].[Description] AS [Description], [Extent6].[Dimensions] AS [Dimensions], [Extent6].[Width] AS [Width], [Extent6].[Height] AS [Height], [Extent6].[IsCustom] AS [IsCustom]
            FROM  [dbo].[CatalogSizes] AS [Extent5]
            INNER JOIN [dbo].[Sizes] AS [Extent6] ON [Extent6].[SizeId] = [Extent5].[SizeId] ) AS [Join3] ON [Limit2].[CatalogId] = [Join3].[CatalogId]
        WHERE ([Limit2].[CatalogId] < 10) AND ([Limit2].[Title] LIKE N’%s%’)) AS [UnionAll1]
    ORDER BY [UnionAll1].[CatalogId1] ASC, [UnionAll1].[C1] ASC

The moral of this story being that you need to still be very careful how you write your queries!  Even with the DbContext buried, there can still be very poorly performing queries implemented.

Summary

Another long article.  Please bear with me, I’m literally exploring these designs as I go, and there’s a good chance I’ve misjudged some aspects of the patterns I’m creating.  I think what I’ve put together thus far is quite sound, but it has been against a fairly simplified data model, with precious little (in terms of quantity) test data.

This will no doubt evolve over the next few articles.  In fact, speaking of the next few articles – the next one is going to be a good one – I’m going to walk through the implementation of Insert, Update and Delete operations on single entities, particularly with a generic approach which I think you will find interesting and powerful.

The article beyond that will focus on bulk operations, and then finally, we’ll return for more on advanced querying.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>