Contents tagged with Pitfalls

  • NHibernate Pitfalls: Specifying Property Types

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

    When you want to specify an NHibernate type (implementation of NHibernate.Type.IType) for a property using mapping by code, you might be tempted to do something like this:

       1: ca.Property(x => x.SomeProperty, x =>
       2: {
       3:     x.Type<StringClobType>();
       4: });

    If you try this, you will get a nasty exception about the type not having a public parameterless constructor. Ouch!

    The thing is: NHibernate types are supposed to be stateless, and therefore it does not make sense to have several instances of a type, that is why NHibernate encourages us to use the static fields defined in the NHibernateUtil class and that is also why most of the built-in types don’t have a public parameterless constructor.

    So, if you want to implement your own types, you can certainly add a public parameterless constructor to them and use the above syntax, but if you are going to use the built-in types, you should instead use:

       1: ca.Property(x => x.SomeProperty, x =>
       2: {
       3:     x.Type(NHibernateUtil.StringClob);
       4: });


  • NHibernate Pitfalls: One Shot Delete and Inverse Collections

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

    When you clear all items in an entity’s collection by calling it’s Clear() method, if it has cascading option all-delete-orphan, NHibernate will then delete (when flushing) all of the collection’s items. That is because it considers them to be children of the entity and we explicitly told NHibernate that the entity no longer has any (very sad, I know! Winking smile).

    Ideally, we would expect NHibernate to only issue a single DELETE, as this:

       1: DELETE FROM children
       2: WHERE parent_id = @p0
       3: /*
       4: AND some_filter_value = @p1
       5: */

    That, however, is not the case for inverse collections, which are the most common ones. In this case, NHibernate issues N DELETEs, one for each entity, which is bad in terms of performance:

       1: DELETE FROM children
       2: WHERE children_id = @p0;
       4: DELETE FROM children
       5: WHERE children_id = @p1;
       7: -- and so on

    The NHibernate reference has an incorrection, in which it states exactly the opposite:

    “one-shot-delete apply to collections mapped inverse="true"”

    There is an open issue at NHibernate JIRA meant to fix this,, but it is not so simple, so we’ll have to wait! Sad smile

    In the meantime, even though it’s not so intuitive, you can use one of the delete methods I describe in this post.


  • NHibernate Pitfalls: Deletes

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

    While I was writing Deleting Entities in NHibernate, I noticed that actually it referred some pitfalls concerning deleting, so I decided to add them to the proper collection.

    There are several ways to delete entities in NHibernate, as you can see in the referenced post. The problems with each approach are:

    • Using Executable HQL: no cascade to related associations occurs, no events are raised;
    • Using Delete with an entity proxy: forces loading of all cascaded lazy associations before deleting; requires a Flush to apply changes (if it needs to be explicit or not depends on you flush settings);
    • Using Delete with a query string: all items are loaded into the session (not a single DELETE statement), no cascade occurs, no events are raised, no notification about the number of affected records and also requires a flush.


  • NHibernate Pitfalls: Get and Filters

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

    This was suggested a long time ago by Kelly Brownsberger (@kbrowns). What happens is, even if you have filters defined, they are not applied on a call to ISession.Get<T>(). This is by design: you are explicitly asking for an entity with some id, so NHibernate just returns you that. On the other hand, static where restrictions defined at the entity level still apply.


  • NHibernate Pitfalls: Criteria and Collections of Non-Entities

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

    Criteria API – and QueryOver, for that matter, which is just a strongly-typed wrapper around Criteria – cannot work with collections of non-entities: elements, components or dictionaries. If you need to work with those, you need to use one of the other APIs – LINQ, HQL or SQL.


  • Entity Framework Pitfalls – Date/Time Operations

    When using Entity Framework, you cannot perform date/time operations as you would normally do in .NET/LINQ to Objects. For instance, the following query throws an exception:

       1: ctx.Projects.Select(x => new { ElapsedTime = DateTime.Now - x.Start }).ToList();

    Instead, you need to use the static methods in SqlFunctions, like DateDiff:

       1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList();

    For a full description of DateDiff’s first parameter, refer to

    If you want to achieve the same result, that is, return a TimeSpan, you need to use LINQ to Objects at the end:

       1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList().Select(x => new { ElapsedTime = TimeSpan.FromHours((Double)x.ElapsedTime) }).ToList();


  • NHibernate Pitfalls: XML Mappings

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

    If you are still using XML mappings – .hbm.xml files –, there’s nothing wrong with that, but you may run into problems.

    First, you will need to add these files as embedded resources in your project, if you are calling Configuration.AddAssembly(), Configuration.AddClass() or you are specifying the name of the assembly in the .config file like this:

       1: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
       2:     <session-factory>
       3:         <property name="connection.driver_class">NHibernate.Driver.Sql2008ClientDriver</property>
       4:         <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
       5:         <property name="connection.connection_string_name">MyConnection</property>
       6:         <mapping assembly="MyAssembly.MyModel" />
       7:     </session-factory>
       8: </hibernate-configuration>

    These methods will look for .hbm.xml files as embedded resources in that assembly:


    Alternatively, you can have the files on the filesystem, but you will have to call Configuration.AddDirectory(), Configuration.AddFile() or Configuration.AddInputStream(), passing the appropriate parameters.

    In either case, the name of each .hbm.xml must end with .hbm.xml (of course!) and must be composed of the name of the associated class including its namespace.

    If you don’t do this, NHibernate will not find your mappings, which will result in runtime errors.


  • NHibernate Pitfalls: Fetch and Paging

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

    NHibernate allows you to force loading additional references (many to one, one to one) or collections (one to many, many to many) in a query. You must know, however, that this is incompatible with paging. It’s easy to see why.

    Let’s say you want to get 5 products starting on the fifth, you can issue the following LINQ query:

       1: session.Query<Product>().Take(5).Skip(5).ToList();

    Will product this SQL in SQL Server:

       1: SELECT
       2:     TOP (@p0) product1_4_,
       3:     name4_,
       4:     price4_
       5: FROM
       6:     (select
       7:         product0_.product_id as product1_4_,
       8: as name4_,
       9:         product0_.price as price4_,        
      10:         ROW_NUMBER() OVER(
      11:     ORDER BY
      12:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      13:     from
      14:         product product0_) as query
      15:     WHERE
      16:         query.__hibernate_sort_row > @p1
      17:     ORDER BY

    If, however, you wanted to bring as well the associated order details, you might be tempted to try this:

       1: session.Query<Product>().Fetch(x => x.OrderDetails).Take(5).Skip(5).ToList();

    Which, in turn, will produce this SQL:

       1: SELECT
       2:     TOP (@p0) product1_4_0_,
       3:     order1_3_1_,
       4:     name4_0_,
       5:     price4_0_,
       6:     order2_3_1_,
       7:     product3_3_1_,
       8:     quantity3_1_,
       9:     product3_0__,
      10:     order1_0__
      11: FROM
      12:     (select
      13:         product0_.product_id as product1_4_0_,
      14:         orderdetai1_.order_detail_id as order1_3_1_,
      15: as name4_0_,
      16:         product0_.price as price4_0_,
      17:         orderdetai1_.order_id as order2_3_1_,
      18:         orderdetai1_.product_id as product3_3_1_,
      19:         orderdetai1_.quantity as quantity3_1_,
      20:         orderdetai1_.product_id as product3_0__,
      21:         orderdetai1_.order_detail_id as order1_0__,
      22:         ROW_NUMBER() OVER(
      23:     ORDER BY
      24:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      25:     from
      26:         product product0_
      27:     left outer join
      28:         order_detail orderdetai1_
      29:             on product0_.product_id=orderdetai1_.product_id
      30:         ) as query
      31: WHERE
      32:     query.__hibernate_sort_row > @p1
      33: ORDER BY
      34:     query.__hibernate_sort_row;

    However, because of the JOIN, what happens is that, if your products have more than one order details, you will get several records – one per order detail – per product, which means that pagination will be broken.

    There is an workaround, which forces you to write your LINQ query in another way:

       1: session.Query<OrderDetail>().Where(x => session.Query<Product>().Select(y => y.ProductId).Take(5).Skip(5).Contains(x.Product.ProductId)).Select(x => x.Product).ToList()

    Or, using HQL:

       1: session.CreateQuery("select od.Product from OrderDetail od where od.Product.ProductId in (select p.ProductId from Product p skip 5 take 5)").List<Product>();

    The generated SQL will then be:

       1: select
       2:     product1_.product_id as product1_4_,
       3: as name4_,
       4:     product1_.price as price4_
       5: from
       6:     order_detail orderdetai0_
       7: left outer join
       8:     product product1_
       9:         on orderdetai0_.product_id=product1_.product_id
      10: where
      11:     orderdetai0_.product_id in (
      12:         SELECT
      13:             TOP (@p0) product_id
      14:         FROM
      15:             (select
      16:                 product2_.product_id,
      17:                 ROW_NUMBER() OVER(
      18:             ORDER BY
      19:                 CURRENT_TIMESTAMP) as __hibernate_sort_row
      20:             from
      21:                 product product2_) as query
      22:         WHERE
      23:             query.__hibernate_sort_row > @p1
      24:         ORDER BY
      25:             query.__hibernate_sort_row);

    Which will get you what you want: for 5 products, all of their order details.