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)

{

    modelBuilder.Entity<Master>().MapToStoredProcedures();

 

    //rest goes here

 

    base.OnModelCreating(modelBuilder);

}

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]()

RETURNS @ReturnTable TABLE

(

    Id INT,

    Email NVARCHAR(50),

    Phone NVARCHAR(50),

    Name NVARCHAR(50)

)

AS

BEGIN

    INSERT @ReturnTable

    SELECT Id, Email, Phone, Name

    FROM dbo.Customer

    RETURN

END

And:

//throws an exception complaining about TVF

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


//likewise

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!

NHibernate Pitfalls: Sharing a Session in Multiple Threads

The NHibernate ISession is not thread-safe, that is, it shouldn’t be shared by multiple threads. The ISessionFactory, however, is thread-safe, and thus it should be used to create ISession instances as needed. Do not worry, creating sessions does not mean that connections to the database are immediately open and kept alive, the ISession implementation only opens database connections when they are necessary and closes them when they cease to be.

Lesser-Known NHibernate Features: Statistics

NHibernate makes available a number of statistics about its work; this includes, among others:

  • All of the queries executed;
  • Number of entities loaded, inserted, updated and deleted;
  • Number of optimistic concurrency misses;
  • Number of second level cache hits and misses;
  • Number of transactions started and committed;
  • Number of connections opened and closed;
  • etc.

This is available per session factory:

var statistics = sessionFactory.Statistics;

And all of these settings can be filtered per entity:

var entityStatistics = sessionFactory.Statistics.GetEntityStatistics("MyEntity");

Statistics can help us diagnose, for example, second level or query cache issues.

If we have no need for them, we can disable them before building the session factory:

cfg.SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, Boolean.FalseString);

Or at runtime:

sessionFactory.Statistics.IsStatisticsEnabled = false;

An advice: do switch it off while on production, it does have an effect on performance.

Lesser-Known NHibernate Features: Dynamic Components

NHibernate, unlike others, has lots of ways to map columns from the database to and from classes. Normally, there is a 1:1 relation between columns and properties, but it doesn’t have to be so: there are, for example, components and dynamic components.

This time we will be looking at dynamic components. Dynamic components allow the usage of a dictionary, where one or several columns from the database will be stored, each in its own entry. This is pretty cool, if we need to add more columns at some point, and we do not want, or can, change the class!

Show me the code, I hear you say:

public class DataStore

{

    public DataStore()

    {

        this.Data = new Dictionary<String, Object>();

    }

 

    public virtual int Id { get; set; }

 

    public virtual IDictionary Data { get; set; }

}

Yes, it will be possible to use generic dictionaries (IDictionary<TKey, TValue>), when pull request for NH-3670 is merged, which should happen soon (NHibernate 4.1).

Now, the mappings:

var mapper = new ConventionModelMapper();

mapper.Class<DataStore>(x =>

    {

        x.Id(y => y.Id, y => { });

        x.Component(y => y.Data, new

        {

            A = 0,

            B = ""

        }, y =>

        {

            y.Property(z => z.A);

            y.Property(z => z.B);

        });

    });

I am setting the template for whatever will be stored in the Data dictionary, in this case, an integer column (A) and a string one (B) as an anonymous object. In NHibernate 4.1, it will be possible to use a dictionary instead (see NH-3704), which will help in making it more dynamic. In the third parameter to Component, we can change the mapping, for example, the physical properties of each column; we can even use ManyToOne instead of Property, so that a particular entry in the dictionary will point to another entity!

A sample usage:

session.Save(new DataStore { Data = new Dictionary<String, Object> { { "A", 1 }, { "B", "two" } } });

Entity Framework Multitenancy

Introduction

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?).

Prerequisites

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);

    });

 

    base.OnModelCreating(modelBuilder);

}

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();

 

    cfg.Requires("Tenant").HasValue(currentTenant);

}

 

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 });

    }

 

    base.OnModelCreating(modelBuilder);

}

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.

Conclusion

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.

Lesser-known NHibernate Features: Filters

Unlike other OR/Ms – which, as always, shall remain unnamed – NHibernate offers a couple of ways to automatic filter results. Basically, we have two options:

  • Static restrictions;
  • Dynamic restrictions, or filters.

Because filters offer everything that static restrictions do and more, we’ll focus on filters.

A filter can specify a restriction, in terms of a SQL clause, to either an entity as a whole (the class, not a specific query) or to a collection (bag, set, list, map, array, etc).

For example, imagine you have a table that holds values that can be translated and a translation table for that purpose:

image

You will want to retrieve only the translation for the current culture. A domain model could look like:

image

We would like to apply a restriction to the Translations property of Translatable, so as to filter the translations by the current culture.

First, we need to create a filter, this is done at Configuration level:

cfg.AddFilterDefinition(new FilterDefinition("CurrentCulture", string.Empty, new Dictionary<string, IType> { { "Culture", NHibernateUtil.String } }, false));

The restriction can be defined on the filter itself, or per entity or collection. In this case, I didn’t specify it on the filter (string.Empty), so I will need to do it at the collection level:

mapper.Class<Translatable>(x =>

{

        //rest goes here

        x.Set(y => y.Translations, y =>

        {

            //rest goes here

            y.Filter("CurrentCulture", z =>

            {

                z.Condition("Culture = :Culture");

            });

        });

    }

);

A filter needs to be explicitly made active, and, if it contains parameters, all of its parameters must be set:

session.EnableFilter("CurrentCulture").SetParameter("Culture", CultureInfo.CurrentCulture.Name);

Now, whenever the Translations collection is retrieved, either through a SELECT or an INNER JOIN, the “Culture = :Culture” restriction – where, of course, :Culture is replaced by the current parameter value – will be applied automatically, together with the foreign key restriction.

The other option is to filter entities as a whole. Remember soft deletes? I wrote two posts on them (here and here). Instead of using static restrictions, we can instead use filters:

cfg.AddFilterDefinition(new FilterDefinition("SoftDeletes", "deleted = 0, new Dictionary<string, IType>(), true));

 

mapper.Class<Record>(x =>

{

    x.Filter("SoftDeletes", y => {});

    x.Set(y => y.Children, y =>

        {

            y.Filter("SoftDeletes", z => {});

        });

});

In this example, I define the restriction string on the filter itself, and I apply it to both the Record entity and its Children collection. This time, no parameters, but we still need to enable the filter before we issue a query:

session.EnableFilter("SoftDeletes");

If for any reason you want to disable a filter, it’s easy:

session.DisableFilter("SoftDeletes");

Or even get its definition:

var filter = sessionFactory.GetFilterDefinition("SoftDeletes");

Enjoy your filters!

Lesser-Known NHibernate Features: Mapping By Convention

Did you know that NHibernate, like other O/RMs out there, allows you to map classes by convention? Yes, it’s true… Smile Let me show you how!

First, you need to create an instance of the appropriately-named ConventionModelMapper:

var mapper = new ConventionModelMapper();

Next, you need to tell it where is the assembly (or assemblies) containing the entities you want to map:

var mappings = mapper.CompileMappingFor(typeof(MyEntity).Assembly.GetExportedTypes());

Finally, you need to add the generated mappings to a Configuration instance:

cfg.AddMapping(mappings);

That’s all it takes! Really! Winking smile

Granted, this is very convenient, but we don’t know much what is happening inside; for example, what id generation strategy is it using? By default, it uses native, which means it will use the native strategy of the database engine currently being used – identity for SQL Server and MySQL, sequence for Oracle and PostgreSQL, etc. If you wish to override this, you certainly can:

mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>

{

    classCustomizer.Id(x =>

    {

        x.Generator(Generators.HighLow);

    });

};

This tells the mapper to use the high-low strategy for all entities.

What if you want to change the default naming of columns and tables? Well, you have two options:

  1. Provide an handler for the BeforeMapClass or BeforeMapProperty events and in it change the name of the physical object:
    mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>

    {

        classCustomizer.Table(this.GetTableName(type.Name));

    };

    
    

    mapper.BeforeMapProperty += (modelInspector, member, propertyCustomizer) =>

    {

        propertyCustomizer.Column(this.GetColumnName(member.LocalMember.Name));

    };

  2. Provide your own implementation of INamingStrategy to NHibernate:
    public class CustomNamingStrategy : INamingStrategy

    {

        public String ClassToTableName(String className)

        {

            return className;

        }

    
    

        public String ColumnName(String columnName)

        {

            return columnName;

        }

    
    

        public String LogicalColumnName(String columnName, String propertyName)

        {

            return columnName;

        }

    
    

        public String PropertyToColumnName(String propertyName)

        {

            return propertyName;

        }

    
    

        public String PropertyToTableName(String className, String propertyName)

        {

            return propertyName;

        }

    
    

        public String TableName(String tableName)

        {

            return tableName;

        }

    }

    
    

    cfg.SetNamingStrategy(new CustomNamingStrategy());

In general, you can trust NHibernate’s judgement, but if you wish, you can override other aspects of the mapping by providing handlers to the many events of ConventionModelMapper. Say, for example, that you want to exclude (or include) only certain classes from an assembly, you can provide an handler for the IsEntity event:

mapper.IsEntity += (type, @default, assemblyName) =>

{

    return typeof(IEntity).IsAssignableFrom(type);

};

Or you want to configure a collection as a set rather than a bag:

mapper.IsBag += (member, @default) =>

{

    return false;

};


mapper.IsSet += (member, @default) =>

{

    return true;

};

Or even set a collection as not lazy and use inner join fetching:

mapper.BeforeMapSet += (modelInspector, member, propertyCustomizer) =>

{

    propertyCustomizer.Lazy(CollectionLazy.NoLazy);

    propertyCustomizer.Fetch(CollectionFetchMode.Join);

};