Contents tagged with Pitfalls

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


  • Entity Framework Pitfalls Index

    Updated on August 25th

    These are the posts on Entity Framework pitfalls I’ve written so far. This post will be updated whenever there are more.