Meet Entity Framework, the Anti-SQL Framework

Entity Framework 6 is coming!

Entity Framework 6 is now ramping up for a release. It brings nice Async functionality, but also gives lots more power to the Code First capability, as well as also bringing EF completely out of the core .NET framework – it’s instead now a standalone NuGet package. So, the story now for creating and modifying your SQL database within EF can be considered to use these four features: –

  1. Get hold of EF via a NuGet package
  2. Create a physical data model based on an object model
  3. Update and evolve your database through code-first migrations
  4. Create a database quickly and easily using LocalDB

No dependency on .NET 4.5, or on a specific version of Visual Studio, or on having a running instance of SQL Server (even SQL Express) as a Windows Service. This sounds all great. We can now create a physical database, create migrations from one version to the next, and write queries in the language that automatically get translated into SQL. In effect, EF6 with code-first allows you to write an application without having to ever look at the database. This may be a somewhat short-sighted or simplistic view – you’ll always need to optimise your DB with indexes and perhaps SPs etc. for high performance corner cases etc. – but for quickly getting up and running, and I would suggest for a fair proportion of your SQL database requirements, EF6 as a framework will sort you out .

Why SQL?

This is all great – the barrier to entry to getting up and running in a short space of time is extremely low. However, I would then raise the question: if you’re using your database in a fashion akin to that above – where it’s just a means to an end – why use a SQL database at all? What are you actually using SQL for? As a way to store and retrieve data into your application. Your queries are written outside of SQL in e.g. LINQ. Your tables are generated outside of T-SQL. You never interact with tables because EF is acting as an ORM that does all the funky mapping for you. So what exactly are you getting from using SQL?

The point is that you can remove almost all of the impedance mismatch between your object model and your data-store in a much simpler way simply by using something like MongoDB. You don’t have a formal schema as such in the database – you simply read and write object graphs to and from a document. The concept of a “mapping” between DB and object model are almost entirely removed. There’s no need to formally upgrade a “schema” e.g. foreign keys, indexes, columns etc. etc – there’s your object model, and nothing else.


I’m not suggesting you go and dump SQL tomorrow if you’ve already invested in it. What I would suggest is that the next time you create a new database you consider why you use SQL and whether you could achieve the same use cases by using a schema-free document database like MongoDB or RavenDB. What are you really using your database for today?

Why Entity Framework renders the Repository pattern obsolete?

A post here on a pattern I thought was obsolete yet I still see cropping up in projects using EF time and time again…

What is a Repository?

The repository pattern – to me – is just a form of data access gateway. We used it to provide both a form of abstraction above the details of data access, as well as to provide testability to your calling clients, e.g. services or perhaps even view models / controllers. A typical repository will have methods such as the following:-

interface IRepository
    T GetById(Int32 id);
    T Insert(T item);
    T Update(T item);
    T Delete(T item);

interface ICustomerRepository : IRepository
    Customer GetByName(String name);

And so on. You’ll probably create a Repository<T> class which does the basic CRUD work for any <T>. Each one of these repositories will delegate to an EF ObjectContext (or DbContext for newer EF versions), and they’ll offer you absolutely nothing. Allow me to explain…

Getting to EF data in Services

Let’s illustrate the two different approaches with a simple example service method that gets the first customer whose name is an arbitrary string. In terms of objects and responsibilities, the two approaches are somewhat different. Here’s the Repository version: –

public class Service
    private readonly ICustomerRepository customerRepository;
    public Customer GetCustomer(String customerName)
        return customerRepository.GetByName(customerName);
public class CustomerRepository : ICustomerRepository
    private readonly DatabaseContext context;
    public Customer GetByName(string customerName)
        return context.Customers.First(c => c.Name == customerName);

Using the Repository pattern, you generally abstract out your actual query so that your service does any “business logic” e.g. validation etc. and then orchestrates repository calls e.g. Get customer 4, Amend name, Update customer 4 etc. etc.. You’ll also invariably end up templating (which if you read my blog regularly you know I hate) your Repositories for common logic like First, Where etc.. – all these methods will just delegate onto the equivalent method on DbSet.

If you go with the approach of talking to EF directly, you enter your queries directly in your service layer. There’s no abstraction layer between the service and EF.

public class ServiceTwo
    private readonly DatabaseContext context;

    Customer GetCustomer(String customerName)
        return context.Customers.First(c => c.Name == customerName);

So there’s now just one class, the service, which is coupled to DatabaseContext rather than CustomerRepository; we perform the query directly in the service. Notice also that Context contains all our repositories e.g. Customers, Orders etc. as a single dependency rather than one per type. Why would we want to do this? Well, you cut out a layer of indirection, reduce the number of classes you have (i.e. the whole Repository hierarchy vs a fake DbContext + Set), making your code quicker to write as well as easier to reason about.

Aha! Surely now we can’t test out our services because we’re coupled to EF! And aren’t we violating SRP by putting our queries directly into our service? I say “no” to both.

Testability without Repository

How do we fix the first issue, that of testability? There are actually many good examples online for this, but essentially, think about this – what is DbContext? At it’s most basic, it’s a class which contains multiple properties, each implementing IDbSet<T> (notice – IDbSet, not DbSet). What is IDbSet<T>? It’s the same thing as our old friend, IRepository<T>. It contains methods to Add, Delete etc. etc., and in addition implements IQueryable<T> – so you get basically the whole LINQ query set including things like First, Single, Where etc. etc.

Because DBSet<T> implements the interface IDbSet<T>, you can write your own one which uses e.g. in-memory List<T> as a backing store instead. This way your service methods can work against in-memory lists during unit tests (easy to generate test data, easy to prove tests for), whilst going against the real DBContext at runtime. You don’t need to play around with mocking frameworks – in your unit tests you can simply generate fake data and place them into your fake DBSet lists.

I know that some people whinge about this saying “it doesn’t prove the real SQL that EF will generate; it won’t test performance etc. That’s true – however, this approach doesn’t try to solve that – what it does try to do is to remove the unnecessary IRepository layer and reduce friction, whilst improving testability – for 90% of your EF queries e.g. Where, First, GroupBy etc., this will work just fine.

Violation of SRP

This one is trickier. You ideally want to be able to reuse your queries across service methods – how do we do that if we’re writing our queries inline of the service? The answer is – be pramatic. If you have a query that is used once and once only, or a few times but is a simple Where clause – don’t bother refactoring for reuse.

If, on the other hand you have a large query that is being used in many places and is difficult to test, consider making a mockable query builder that takes in an IQueryable, composes on top of it and then returns another IQueryable back out. This allows you to create common queries yet still be flexible in their application – whilst still giving you the ability to go directly to your EF context.


Testability is important when writing EF-based data-driven services. However, the Repository pattern offers little when you can write your services directly against a testable EF context. You can in fact get much better testability from an service-with-an-EF-context based approach than just with a repository, as you can test out your LINQ queries against a fake context, which at least proves your query represents what you want semantically. It’s still not a 100% tested solution, because your code does not test out the EF IQueryable provider – so it’s important that you still have some form of integration and / or performance tests against your services.

Can’t wait for C#4…

Following on from my posts on dynamic typing… this week I wrote a quick app to import some data from a load of XML files to a load of tables in a SQL database. The columns in the tables and the attributes in the xml file have a 1:1 mapping.

So I firstly wrote some code which goes through a given table and reads the schema from the database and creates a C# type based on the schema (using sp_columns). Then I placed a ColumnAttribute on each column etc. etc., and then goes through the task of iterating through the XML data and creating objects of that new type, copy the data across (doing the odd data type conversion along the way), and finally use the standard LinqToSql DataContext to insert the data into the DB.

But a lot of the code was somewhat ugly – messing about with TypeBuilders and FieldBuilders and the like – I’m sure that I could have done something much more elegant in C#4…. none of it was compile-time safe, it was all using reflection etc..

But it did work great anyway – apparently was used to migrate a good few thousand records of XML data into SQL relatively quickly 🙂

Installing SQL Management Studio 2008 Express

When installing SQL Express 2008, you may get the following error if you already have VS 2008 installed:

Rule “Previous releases of Microsoft Visual Studio 2008” failed.

A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.


I’ve read a load of blogs about this issue, but none of them solved the problem that I was having, as I already had VS 2008 SP1 installed.

Galin Iliev’s excellent blog post comes close but doesn’t quite hit the bullseye (although I believe that his solution would probably work as well). The problem is that I have Visual Studio 2008 database edition installed as well. So, from Galin’s post, I found that the following registry key stores version settings of VS:


Going in there, I see not one, but three sub-keys: –


I’ve annotated the above with what I guess the different keys represent.

Inside each of those keys are a load of properties, one of which is named SP. VSTD is set to 1 (correctly), but VSDB and VSDBGDR are both set to 0.

Solution: Ensure that all three keys have the “SP” value set to 1.

It appears that the SQL installer expects all of them to be set to 1 otherwise it won’t install…

P.S. Probably best to set them back to 0 after the install 🙂

SQL Express – more aggravation

So, I installed SQL Express 2005 only to find that when removing SQL Express 2008, and then manually removing those two components (2008 object model and 2008 native client) that I’d somehow “broken” 2005 (even though I hadn’t yet installed it!). I guess there are some assemblies that are overwritten by the 2008 install that are backwards compatible – so when I uninstalled the 2008 items that the 2008 uninstall left behind, it screwed me completely.

So, reinstalled them – and finally got SQL Express 2005 working. Until I accidentally changed the default database for my Windows account to my custom database that I was working on and then deleted it by accident! Now I can’t even log in to SQL. So I had to use SQLCMD to log in and change my default database back to master.

Phew…. all this just to prepare a demo of a few features of C# 3 and LINQ!

SQL Express 2008 – Frustration in 10 easy steps!

I was looking for the SQL Express 2005 edition and came across the 2008 one. So I thought I’d give it a try – what a waste of time!

  1. Ran the installer for SQL Express 2008
  2. Wouldn’t install because I didnt have the latest version of Windows Installer (4.5).
  3. Downloaded Windows Installer 4.5
  4. Installed Windows Installer 4.5
  5. Rebooted
  6. Wouldn’t install because I didn’t have Windows Powershell installed
  7. Downloaded Windows Powershell
  8. Installed Windows Powershell
  9. Ran the installer for SQL Express 2008
  10. Wouldn’t install because I didn’t have Visual Studio 2008 SP1 installed – except I did!

Gave up after this and just got the old 2005 version of SQL Express. But not before I had to manually uninstall half of the rubbish the 2008 installer left behind on my PC.

Deploying SQL Compact databases – Part III

I ended up using ClickOnce deployment instead of Windows Installer after all! This is because I uninstalled the beta of VS2008 and installed the "proper" version of VS 2008 Express – which doesn’t support Setup projects.

ClickOnce is fairly easy to set up – the only thing that I couldn’t work out was how to get it to notice Content files in dependant projects for deployment purposes i.e. the .sdf file that is the database for the application is in another project, and doesn’t get picked up by the ClickOnce publisher. So I had to move the .sdf file into my main project that is published – not ideal but not the end of the world.

I have also then found that after installing and running the first time, occasionally after the install, the database file can’t be found by the application. Once you run it a second time, it works fine – but the first time on install it doesn’t always work. Weird.