Contents tagged with Pitfalls

  • 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.

    Read more...

  • 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.

    Read more...

  • 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.

    Read more...

  • Entity Framework Pitfalls: Non Public Entity Sets Setter

    When defining your entity sets in a Code First context (DbContext), you declare your entity sets as properties of the DbSet<T> type. Weird as it may seem, these properties need to be declared with both a public getter and setter. It really doesn’t make much sense, because you wouldn’t want to set these properties from outside the context, but that’s how it is. So, this won’t work:

       1: public DbSet<Product> Products { get; protected set; }

    While this does:

       1: public DbSet<Product> Products { get; set; }

    You can have non-public setters provided you initialize them explicitly in the constructor by calling Set<T>, because EF won't do it for you. Don’t ask me why! Winking smile

    Read more...

  • Entity Framework Pitfalls: String Length Validation

    If you want to validate the maximum number of characters that a string property can take, you might be lured into using MaxLengthAttribute. However, this won’t give you what you want: what this attribute does is, when a model is being generated, it provides the maximum length of the string field in the database, but does not perform any kind of pre-insert or pre-update validation. For that, you need to use StringLengthAttribute. This one is indeed a validation attribute, inheriting from ValidationAttribute, and will be called when EF is about to persist your entity, or when ASP.NET MVC validates a model as a consequence of a post. You can specify both the minimum (MinimumLength) as well as the maximum length (MaximumLength).

    Read more...

  • Entity Framework Pitfalls: Collections of Types Other Than ICollection<T> Are Ignored

    If you expose in your entity a collection of a type other than ICollection<T> (or one inheriting from it, such as IList<T>) then it will be ignored. This is a bit absurd, because you might want to expose a collection as read only, where you would probably use IEnumerable<T>. For the record, NHibernate allows that.

    A possible solution is to have a non-public field of type ICollection<T> and to expose it as an IEnumerable<T> property. The problem is that you won’t be able to use the property in LINQ queries, because it is not mapped. And conventions won’t help either, because you cannot configure collections there.

    Read more...

  • NHibernate Pitfalls: HQL Queries With Joins

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

    Normally, in an HQL query, you can omit the select clause, that is, the two queries are identical:

       1: var q1 = session.CreateQuery("from Product"); //both return IList<Product>
       2: var q2 = session.CreateQuery("select p from Product p");

    However, if you add joins, then it’s a whole different matter:

       1: var q3 = session.CreateQuery("from Product p left join p.OrderDetails");             //returns IList<Object[]>
       2: var q4 = session.CreateQuery("select p from Product p left join p.OrderDetails");    //returns IList<Product>
       3:  

    Worse, queries q3 and q4 will not filter distinct root entities, meaning, you will get the cartesian product of Product x OrderDetail.

    So, you will need to select the root entity (select p), plus add a distinct entity result transformer (Transformers.DistinctRootEntity) to get what you want:

       1: var q5 = session.CreateQuery("select p from Product p left join p.OrderDetails").SetResultTransformer(Transformers.DistinctRootEntity);    //IList<Product>

    This doesn’t happen with LINQ, it always performs a distinct root entity selection, but it also happens with Criteria and QueryOver.

    Thanks to Alexander Zaytsev (@hazzik) who reminded me of this!







    Read more...

  • Entity Framework Pitfalls: Registering Custom Database Functions for LINQ

    Like I said in my previous post, it’s not enough to add a DbFunctionAttribute attribute to a method to have it call a database function. If the function is not a built-in one, it will not be registered in the Entity Framework provider manifest for SQL Server, so it will require registration in the model. You might think, just by looking at the methods in SqlFunctions that all that it took was to add this attribute, but you would be wrong.

    Read more...

  • NHibernate Pitfalls: Refreshing Manually Created Entities Does Not Bring Lazy Properties of Base Types

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

    Suppose you have some properties of an entity, including its id, and you want to refresh its state from the database:

       1: var product = new Product { ProductId = 1 };
       2:  
       3: //product.Image is null
       4:  
       5: session.Refresh(product);
       6:  
       7: //product.Image is null

    Image is a Byte[] and is configured as lazy. The problem is that NHibernate is not capable of returning a proxy for it, because the entity itself is not a proxy.

    This does not happen for associated entities (one-to-one, many-to-one, one-to-many and many-to-many):

       1: var order = new Order { OrderId = 1 };
       2:  
       3: //order.Customer is null
       4:  
       5: session.Refresh(order);
       6:  
       7: //order.Customer is a proxy and accessing the property will load it

    In this case, Customer is another entity, and NHibernate can assign the Order.Customer property a proxy to it.

    Because of this problem, I created a simple extension method that loads all properties. It is even smart enough to use proxies, if we so require it:

       1: public static void InitializeLazyProperties(this ISession session, Object entity, Boolean useProxyWhenPossible = true)
       2: {
       3:     if (entity.IsProxy() == true)
       4:     {
       5:         //if entity is a proxy, use the default mechanism
       6:         NHibernateUtil.Initialize(entity);
       7:     }
       8:     else
       9:     {
      10:         var metadata = session.SessionFactory.GetClassMetadata(entity.GetType());
      11:         var propertiesToLoad = new List<String>();
      12:  
      13:         for (var i = 0; i < metadata.PropertyNames.Length; ++i)
      14:         {
      15:             if (metadata.GetPropertyValue(entity, metadata.PropertyNames[i], session.ActiveEntityMode) == null)
      16:             {
      17:                 if ((metadata.PropertyTypes[i].IsEntityType == false) || (useProxyWhenPossible == false))
      18:                 {
      19:                     //either load the value
      20:                     propertiesToLoad.Add(metadata.PropertyNames[i]);
      21:                 }
      22:                 else
      23:                 {
      24:                     //or the id of the associated entity
      25:                     propertiesToLoad.Add(String.Concat(metadata.PropertyNames[i], ".id"));
      26:                 }
      27:             }
      28:         }
      29:  
      30:         var hql = new StringBuilder();
      31:         hql.Append("select ");
      32:         hql.Append(String.Join(", ", propertiesToLoad));
      33:         hql.AppendFormat(" from {0} where id = :id", entity.GetType());
      34:  
      35:         var query = session.CreateQuery(hql.ToString());
      36:         query.SetParameter("id", metadata.GetIdentifier(entity, session.ActiveEntityMode));
      37:  
      38:         var result = query.UniqueResult();
      39:         var values = result as Object[] ?? new Object[] { result };
      40:  
      41:         for (var i = 0; i < propertiesToLoad.Count; ++i)
      42:         {
      43:             var parts = propertiesToLoad[i].Split('.');
      44:             var value = values[i];
      45:             var propertyName = parts.First();
      46:  
      47:             if (parts.Length > 0)
      48:             {
      49:                 var propertyIndex = Array.IndexOf(metadata.PropertyNames, propertyName);
      50:                 var propertyType = metadata.PropertyTypes[propertyIndex].ReturnedClass;
      51:  
      52:                 //create a proxy
      53:                 value = session.Load(propertyType, values[i]);
      54:             }
      55:  
      56:             metadata.SetPropertyValue(entity, propertyName, value, session.ActiveEntityMode);
      57:         }
      58:     }
      59: }

    Of course, it requires that at leat the id property is set. It can be used as:

       1: var order = new Order { OrderId = 360448 };
       2:  
       3: session.InitializeLazyProperties(order);

    Have fun! Winking smile

    Read more...

  • Entity Framework Pitfalls: Batch Modifications

    Entity Framework does not support batch modifications, that is, UPDATEs or DELETEs of several entities at once. Neither classic nor Code First, not even Entity-SQL.

    For UPDATEs, you have to load each entity and modify it to your liking and then save changes.

    For DELETEs, you don’t need to load an entity, just call Remove with a proxy entity with an assigned primary key property, like this:

       1: var entityToDelete = new MyEntity { Id = 100 };
       2: ctx.MyEntities.Remove(entityToDelete);
       3: ctx.SaveChanges();

    Of course, you can remove several entities at once, just make sure you only call SaveChanges only after Removeing all of them.

    Other alternatives exist:

    1. Plain old SQL, just call Database.ExecuteSqlCommand;
    2. Use a third-party library, such as EntityFramework.Extended, which offers nice strongly-typed methods for both UPDATEs and DELETEs.

    Read more...