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.

SharePoint Pitfalls: Master Pages in Page Layouts

When you deploy a page layout, you may want to add your own master page as well. I find it useful to add my own custom master pages and reference them directly, in the same or a dependent feature. You might be surprised, however, that it doesn’t work exactly how you’d expect!

The issue is, page layouts will ignore silently anything in the MasterPageFile attribute if it isn’t one of the standard tokens for the system or custom master pages. ~masterurl/default.master and ~masterurl/custom.master. The solution is to have a code behind class and specify the master page in the OnPreInit method (anywhere else won’t work):

protected override void OnPreInit(EventArgs e)

{

    base.OnPreInit(e);

 

    this.MasterPageFile = "~site/_catalogs/masterpage/CustomMasterPage.master":

}

SharePoint Pitfalls Index

This page will list all of my posts dedicated to SharePoint pitfalls. It will be updated regularly.

  1. Creating a Visual Studio Project Without SharePoint Locally Installed
  2. Save Publishing Site as Template Option Missing
  3. Publishing Pages in Document Libraries Other Than Pages

SharePoint Pitfalls: Save Publishing Site as Template Option Missing

If you want to save a publishing site as a template, so that it can be used to create other sites, you may find it surprising that the option is missing from the site settings page:

image

I don’t know exactly why, but publishing sites hide this option, however, it’s not difficult to get around it: just navigate to /_layouts/15/savetmpl.aspx. But, wait, what if you get this?

image

Easy, easy. Open your site in SharePoint Designer, click Site Options:

image

and change the value of property SaveSiteAsTemplateEnabled from false to true:

image

And now you will be able to access savetmpl.aspx and save your site.

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.

Entity Framework Pitfalls: Command Interceptors and Identity Keys

The IDbCommandInterceptor (sorry, no official documentation) interface was introduced in Entity Framework 6 as part of the new interception and logging API, and allows the interception of the SQL and its parameters that are sent to the database as the result of DbContext CRUD operations.

It offers two methods for each of the basic ADO.NET operations, those defined in DbCommand, one called before the operation is executed, and the other called afterwards:

  • ExecuteNonQuery (UPDATEs, INSERTs, DELETEs) –> NonQueryExecuting, NonQueryExecuted;
  • ExecuteScalar (SELECTs returning a single value) –> ScalarExecuting, ScalarExecuted;
  • ExecuteReader (SELECTs returning rows) –> ReaderExecuting, ReaderExecuted.

As usual, the methods ending with “ing” are executed before and those ending with “ed” are executed afterwards, always synchronously.

One might naively assume that INSERTs would always trigger a NonQueryExecuting/NonQueryExecuted call, and indeed it is so, unless we have an IDENTITY primary key, in which case, Entity Framework will instead call ReaderExecuting/ReaderExecuted. It’s easy to understand why: when we use an IDENTITY, we need to retrieve the generated value immediately after the INSERT, hence Entity Framework will generate code like:

   1: INSERT INTO [dbo].[SomeTable] (...)

   2: VALUES (...)

   3: SELECT [Id]

   4: FROM [dbo].[SomeTable]

   5: WHERE @@ROWCOUNT > 0 AND [Id] = SCOPE_IDENTITY()

The INSERT and SELECT are combined in the same command, which justifies the choice of ReaderExecuting/ReaderExecuted. Because in Entity Framework we always use IDENTITY – don’t we? –, it’s ReaderExecuting/ReaderExecuted that you should be implementing if you want to change the INSERT SQL or its parameters.

Entity Framework Pitfalls: Mapping Discriminator Columns

When you use a discriminator column, that is, a column that holds a value that tells Entity Framework what type the row refers to, or what restriction it shall use when querying the entity, you cannot map this column as a property in your entity.

For example, imagine you want to use “soft deletes”, that is, use a database column to represent the “deleted” state of a record instead of actually physically deleting it from the table; you might have an IS_DELETED column of some integer type, that would hold either a 1 or a 0, depending on whether the record is deleted (1) or not (0). In that case, you would build a mapping like this in OnModelCreating:

   1: modelBuilder.Entity<MySoftDeletableEntity>().Map<MySoftDeletableEntity>(m => m.Requires("is_deleted").HasValue(0));

However, you won’t be able to have a corresponding IsDeleted property in the MySoftDeletableEntity, because Entity Framework will throw an exception complaining about it. It is somewhat sad, because it might be useful to refer to it, but that’s the way it is.

NHibernate Pitfalls: Versioned Entities Are Not Batcheable

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

This is a problem similar to the one with native id generators. Basically, because there are different versioning strategies – timestamp, counter, native, etc, – NHibernate needs to issue a SELECT after a versioned entity is INSERTEd or UPDATEd. While this wouldn’t be necessary when NHibernate manages the version on the “client side”, like:

   1: UPDATE MyEntity SET Version = 2 WHERE ...;

   2: //or

   3: UPDATE MyEntity SET Version = GETDATE() WHERE ...;

But not if the version is handled on the database side, like when using SQL Server’s ROWVERSION/TIMESTAMP columns or Oracle’s ORA_ROWSCN pseudo-columns. In these cases, NHibernate needs to issue a SELECT after each INSERT or UPDATE:

   1: UPDATE MyEntity SET ... WHERE ...;

   2: SELECT Version FROM MyEntity WHERE ...;

This breaks batching, because it needs to be done immediately after each INSERT/UPDATE.