Entity Framework Pitfalls: TransactionScope

Most people using Entity Framework use the TransactionScope API to manage database transactions. This is a powerful API, that can be used to enlist .NET APIs and even WS-*-compatible web services in transactions, that may well be distributed. Microsoft used to recommend it for working with EF, but isn’t doing it anymore, or, at least, developers need to understand a thing or two.

Most modern APIs offer asynchronous methods in addition, or instead, of blocking ones, and Entity Framework is a good example – just think of SaveChangesAsync, FindAsync and ToListAsync. The problem with TransactionScope prior to version 4.5.1 was that it didn’t handle well transaction-enabled methods running in other threads – which is what EF’s asynchronous methods do. In this version, Microsoft introduced the

TransactionScopeAsyncFlowOption flag, which can be used to tell TransactionScope to flow transactions across async threads.

Also, if we open two database connections inside a TransactionScope, the transaction gets automatically promoted to a distributed one. The problem is, distributed transactions require Microsoft Distributed Transaction Coordinator service running, and it is not available in Azure, for example (Web Apps).

So, the key here is: if we cannot be certain of the environment where EF will run, which may include pre-.NET 4.5.1 or Azure, we should use explicit transactions instead. It’s not that difficult, it’s just a matter of calling Database.BeginTransaction() and either committing or rolling back/disposing of the returned object when we are done with it.

Entity Framework 7: What’s New?

The Microsoft WebCamp 2015 event (Portugal) is now over! Another day packed with lots of interesting sessions and, above all, interesting people! Smile

As usual, here is the slide deck for my presentation, Entity Framework 7: What’s New?. In the shared folder you will also find the sample code. *Please*, do drop me a line if you want to discuss any of the topics!

Thanks to all who dropped by, and to Luís Calado (@elcalado) and João Almeida (@jalmeida) from Microsoft Portugal for inviting me! Winking smile

Entity Framework Pitfalls – Composite Primary Keys

When you have a composite primary key, you will get two properties on your POCO class that together represent the id. In this case, conventions won’t be enough, so you need to mark both properties with the KeyAttribute attribute:

public class MyCompositeEntity



    public int PartialKey1 { get; set; }



    public int PartialKey2 { get; set; }


That won’t suffice, though. You also need to specify the order by which the columns should be used, through the ColumnAttribute’s Order property:

public class MyCompositeEntity



    [Column(Order = 0)]

    public int PartialKey1 { get; set; }



    [Column(Order = 1)]

    public int PartialKey2 { get; set; }


Mind you, the order is zero-based. It will be used in two places:

  • When generating the primary key index;
  • When querying using the Find method of the DbSet<T>:
var entity = ctx.MyCompositeEntities.Find(

    1,    //PartialKey1 (Order = 0)

    2     //PartialKey2 (Order = 1)


Entity Framework Pitfalls: DbConfiguration Classes Are Automatically Loaded

Entity Framework, since version 6, allows us to specify certain aspects of its configuration by code through a DbConfiguration-derived class. This configuration can be specified in one of three ways:

However, if there is a DbConfiguration-derived class in the same assembly as your DbContext, it will be automatically set as the configuration class, provided that:

  • It is public;
  • It is non-abstract;
  • It has a public parameterless constructor.

So, a DbConfiguration in the same assembly as the DbContext is automatically loaded. If there is more than one, the DbContext constructor will throw an exception.

Entity Framework Pitfalls: Using Stored Procedures for Basic Operations

Entity Framework, as of version 6, allows the usage of stored procedures for its CUD (Create, Update and Delete) operations. We just need to override the DbContext’s OnModelCreating method and call MapToStoredProcedures.

protected override void OnModelCreating(DbModelBuilder modelBuilder)




    //rest goes here




The problem is, this is all or nothing: you cannot use a stored procedure for just one of the operations, you need to use stored procedures for all operations. And, no, it is not possible (yet) to use custom SQL, just stored procedures.

Entity Framework Pitfalls: Lazy Loading and Proxy Creation

There are two properties that control the ability to lazy load navigation properties:

  • LazyLoadingEnabled: controls whether navigation properties marked as virtual will be lazy loaded, that is, loaded on demand when they are accessed;
  • ProxyCreationEnabled: Entity Framework will create dynamic proxy classes for unsealed types with virtual properties, whenever it loads an instance from the database or it is asked to create an instance (Create method).

The problem is: for the most cases, we need both properties to be true. If they are not, navigation properties are only loaded if:

  1. Eagerly loaded through the Include extension method;
  2. Explicitly loaded through the Load method (collections only).

There are cases, however, when you wouldn’t want to have proxies:

  • When exposing entities through WCF, because since it knows nothing about the dynamic generated proxy types;
  • When using, for example, AutoMapper, to convert from one type to another, for the same reason.

The one reason why you would want a proxy without lazy loading is for self-tracking changes, that is, the proxy detects changes made to it instead of having the DbChangeTracker class (the DbContext.ChangeTracker property) doing it.

Sessão Netponto: Entity Framework 7

A minha apresentação sobre Entity Framework 7 feita na 6ª reunião presencial da Netponto em Coimbra está disponível aqui.

Os tópicos cobertos foram:

  • Novidades do Entity Framework 7
  • Entity Framework 7 vs NHibernate

Brevemente o video e a apresentação estarão disponíveis no site da Netponto.

Obrigado a todos os que estiveram presentes! Winking smile

Entity Framework Pitfalls: Table Valued Functions

As of Entity Framework 6.x, Table Valued Functions are not supported, which is really a pity.

For example, the following does not work:

CREATE FUNCTION [dbo].[GetCustomers]()



    Id INT,

    Email NVARCHAR(50),

    Phone NVARCHAR(50),

    Name NVARCHAR(50)




    INSERT @ReturnTable

    SELECT Id, Email, Phone, Name

    FROM dbo.Customer




//throws an exception complaining about TVF

var customers1 = ctx.Database.SqlQuery(typeof(Customer), "[dbo].[GetCustomers]");


var customers2 = ctx.Database.Customers.SqlQuery("[dbo].[GetCustomers]");

However, you can do this:

var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");

And it works!

Entity Framework Multitenancy


Multitenancy is currently a hot topic in web development. Azure, SharePoint, and many other frameworks are offering multitenant options, because it totally makes sense to make a better use of a server by hosting many different services.

When it comes to data, there are usually three different strategies:

  • Separate databases: each tenant is stored in its own database; different connection strings must be provided, one for each tenant;
  • Shared database, separate schemas: all tenant’s data live in the same database and even share table names, but in different schemas;
  • Shared database, shared schema tenants share the same physical tables,  but use a discriminator column for distinguishing between them.

Let’s explore how we can use each of these techniques in Entity Framework (Code First, of course – is there another?).


First, we need to have a way to obtain the current tenant, specifically, a tenant id or code. To simplify, let’s just assume a simple interface:

public static class TenantConfiguration


    public static String GetCurrentTenantId()


        //doesn't matter



You are free to implement this in any way you want.

We’ll also have a data context:

public class MultitenantContext : DbContext


    public DbSet<MultitenantEntity> MultitenantEntities { get; set; }


Separate Databases

We need to inject the different connection strings through the constructor:

public MultitenantContext() : base(GetConnectionString())




private static String GetConnectionString()


    var currentTenant = TenantConfiguration.GetCurrentTenantId();

    return ConfigurationManager.ConnectionStrings[currentTenant].ConnectionString;


This is a simple strategy that relies on having one connection string per tenant id, but others exist, of course.

Shared Database, Separate Schemas

Another option is to have each tenant in its own schema. For that, we need to leverage the OnModelCreating method for configuring the model:

protected override void OnModelCreating(DbModelBuilder modelBuilder)


    var currentTenant = TenantConfiguration.GetCurrentTenantId();


    modelBuilder.Types().Configure(x =>


        x.ToTable(x.ClrType.Name, currentTenant);





Again, a simple example: each type gets mapped to an equally-named table and to a schema that is identical to the tenant id. Pay attention to this: SQL Server and other RDBMSs allows users to have a default schema, so in theory, if you use integrated security, you may leave out the explicit schema. However, Entity Framework will always include the schema with the database objects, so you have to explicitly configure it, as we have.

Shared Database, Shared Schema

The final option depends on a discriminator column that is not mapped, but contains a different value for each tenant. Again, we need to configure the model accordingly (warning: reflection ahead):

private static void Map<T>(EntityMappingConfiguration<T> cfg) where T : class


    var currentTenant = TenantConfiguration.GetCurrentTenantId();





protected override void OnModelCreating(DbModelBuilder modelBuilder)


    var modelConfiguration = modelBuilder.GetType().GetProperty("ModelConfiguration", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelBuilder, null);

    var entities = modelConfiguration.GetType().GetProperty("Entities", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelConfiguration, null) as IEnumerable<Type>;


    foreach (var entity in entities)


        var entityTypeConfiguration = modelBuilder.GetType().GetMethod("Entity").MakeGenericMethod(entity).Invoke(modelBuilder, null);

        var mapMethod = entityTypeConfiguration.GetType().GetMethods().First(m => m.Name == "Map");


        var localMethod = this.GetType().GetMethod("Map", BindingFlags.Static | BindingFlags.NonPublic).MakeGenericMethod(entity);

        var delegateType = typeof(Action<>).MakeGenericType(localMethod.GetParameters().First().ParameterType);


        var del = Delegate.CreateDelegate(delegateType, localMethod);


        mapMethod.Invoke(entityTypeConfiguration, new Object[] { del });





This code is required because we need to iterate through all mapped entities, and Entity Framework doesn’t expose everything as public APIs. Some parts could be cached for performance or slightly improved, but I leave that as an exercise to you. In the end, all queries to entities will take an additional restriction “WHERE Tenant = @tenant”, where @tenant will take a different value for each tenant and Tenant is a physical column that isn’t mapped, nor does it need to be.


We’ve seen several techniques for mapping multitenant Entity Framework contexts; ultimately, the one we choose will depend on a number of factors. It will mostly be about having more or less isolation of your data.

MVP Showcase 2015

UPDATE: you can find the slide deck here.

(This post is in Portuguese and in English)

As I mentioned earlier, the Portuguese MVPs are organizing the MVP Showcase 2015 event, this Wednesday, April 22nd, in Lisbon, at Microsoft’s premises.

I will be presenting on Entity Framework 7, and the slide deck will be available here after the event. Besides myself, the other presenters will be:

The keynote will come from Cristina Gonzalez Herrero (@crisgherrero), the head of the MVP program for Portugal, Spain and Italy.

Looking forward to seeing you all there!

Como disse antes, os MVPs portugueses estão a organizar o evento MVP Showcase 2015, na próxima quarta-feira, 22 de Abril, em Lisboa, nas instalações da Microsoft.

Eu vou fazer uma apresentação sobre o Entity Framework 7, e os slides irão estar disponíveis aqui após o evento. Além de mim, os outros apresentadores serão:

A apresentação será feita pela Cristina Gonzalez Herrero (@crisgherrero), a responsável do programa MVP para Portugal, Espanha e Itália.

Espero ver-vos todos por lá!