Contents tagged with NHibernate

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

    image

    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.

    Read more...

  • NHibernate Pitfalls: Loading Foreign Key Properties

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

    When saving a new entity that has references to other entities (one to one, many to one), one has two options for setting their values:

    • Load each of these references by calling ISession.Get and passing the foreign key;
    • Load a proxy instead, by calling ISession.Load with the foreign key.

    So, what is the difference? Well, ISession.Get goes to the database and tries to retrieve the record with the given key, returning null if no record is found. ISession.Load, on the other hand, just returns a proxy to that record, without going to the database. This turns out to be a better option, because we really don’t need to retrieve the record – and all of its non-lazy properties and collections -, we just need its key.

    An example:

       1: //going to the database
       2: OrderDetail od = new OrderDetail();
       3: od.Product = session.Get<Product>(1);    //a product is retrieved from the database
       4: od.Order = session.Get<Order>(2);        //an order is retrieved from the database
       5:  
       6: session.Save(od);
       7:  
       8: //creating in-memory proxies
       9: OrderDetail od = new OrderDetail();
      10: od.Product = session.Load<Product>(1);    //a proxy to a product is created
      11: od.Order = session.Load<Order>(2);        //a proxy to an order is created
      12:  
      13: session.Save(od);

    So, if you just need to set a foreign key, use ISession.Load instead of ISession.Get.

    Read more...

  • 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:         product0_.name 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:         product0_.name 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:     product1_.name 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.

    Read more...

  • Entity Framework Pitfalls – Deleting Orphans

    The idea for this post came from Jimmy Bogard, of AutoMapper fame.

    Entity Framework does not really have the notion of parents and children, only of relations. Some of these relations can be mandatory, others can be optional. It is possible to specify cascade delete rules, so that when an entity is removed from the database, all entities that depend on it are also removed. However, what happens if we remove a related (child) entity from it’s parent’s children collection? Consider this code:

       1: using (var ctx = new OrdersContext())
       2: {
       3:     var customer = ctx.Customers.First();
       4:     customer.Orders.Remove(customer.Orders.First());
       5:  
       6:     ctx.SaveChanges();
       7: }

    What we are doing here is removing the association between an Order and its Customer. Because an Order cannot exist without a Customer, we are turning it into an orphan. It is basically the same as:

       1: var order = ctx.Orders.First();
       2: order.Customer = null;

    Do note that we are not deleting anything, just removing the related (parent) reference. In this case, Entity Framework will, of course, fail, complaining about the missing required relation.

    Entity Framework has really no workaround for this – at least, not that I know of – so you have to delete the child entities explicitly:

       1: var customer = ctx.Customers.First();
       2: var order = customer.Orders.First();
       3:  
       4: customer.Orders.Remove(order);
       5: ctx.Orders.Remove(order);
       6:  
       7: ctx.SaveChanges();

    As a side note, NHibernate handles this very well, by setting the cascade option to delete-orphan.

    Read more...

  • Entity Framework Pitfalls – Deleting Orphans

    The idea for this post came from Jimmy Bogard, of AutoMapper fame.

    Entity Framework does not really have the notion of parents and children, only of relations. Some of these relations can be mandatory, others can be optional. It is possible to specify cascade delete rules, so that when an entity is removed from the database, all entities that depend on it are also removed. However, what happens if we remove a related (child) entity from it’s parent’s children collection? Consider this code:

       1: using (var ctx = new OrdersContext())
       2: {
       3:     var customer = ctx.Customers.First();
       4:     customer.Orders.Remove(customer.Orders.First());
       5:  
       6:     ctx.SaveChanges();
       7: }

    What we are doing here is removing the association between an Order and its Customer. Because an Order cannot exist without a Customer, we are turning it into an orphan. It is basically the same as:

       1: var order = ctx.Orders.First();
       2: order.Customer = null;

    Do note that we are not deleting anything, just removing the related (parent) reference. In this case, Entity Framework will, of course, fail, complaining about the missing required relation.

    Entity Framework has really no workaround for this – at least, not that I know of – so you have to delete the child entities explicitly:

       1: var customer = ctx.Customers.First();
       2: var order = customer.Orders.First();
       3:  
       4: customer.Orders.Remove(order);
       5: ctx.Orders.Remove(order);
       6:  
       7: ctx.SaveChanges();

    As a side note, NHibernate handles this very well, by setting the cascade option to delete-orphan.

    Read more...

  • Making Better Use of the NHibernate HiLo Generator

    Introduction

    NHibernate’s HiLo (High-Low) id generation algorithm is one of the most commonly used, and for good reasons:

    • It is database-independent, that is, does not rely on any database-specific functionality such as SQL Server’s IDENTITY and Oracle’s SEQUENCE;
    • It allows batching of inserts;
    • It complies with the Unit of Work pattern, because it sends all writes at the same time (when the session is flushed);
    • Your code does not need to know or care about it.

    Now, this post does not intent to explain this algorithm in depth, for that I recommend the NHibernate HiLo Identity Generator article or Choosing a Primary Key: Natural or Surrogate?, for a more in-depth discussion of id generation strategies. Here I will talk about how to make better use of the NHibernate implementation.

    Max Low

    First of all, you can configure the max low value for the algorithm, using by code mapping, like this:

       1: x.Generator(Generators.HighLow, g => g.Params(new { max_lo = 100 }));

    The default max low value is 32767. When choosing a lower or a higher value, you should take into consideration:

    • The next high value is updated whenever a new session factory is created, or the current low reaches the max low value;
    • If you have a big number of inserts, it might pay off to have a higher max low, because NHibernate won’t have to go to the database when the current range is exhausted;
    • If the session factory is frequently restarted, a lower value will prevent gaps.

    There is no magical number, you will need to find the one that best suits your needs.

    One Value for All Entities

    With the default configuration of HiLo, a single table, row and column will be used to store the next high value for all entities using HiLo. The by code configuration is as follows:

       1: this.Id(x => x.SomeId, x =>
       2: {
       3:     x.Column("some_id");
       4:     x.Generator(Generators.HighLow);
       5: });

    The default table is called HIBERNATE_UNIQUE_KEY, and its schema is very simple:

    image

    Whenever NHibernate wants to obtain and increment the current next high value, it will issue SQL like this (for SQL Server):

       1: -- select current value
       2: select next_hi
       3: from hibernate_unique_key with (updlock, rowlock)
       4:  
       5: -- update current value
       6: update hibernate_unique_key
       7: set next_hi = @p0
       8: where next_hi = @p1;

    There are pros and cons to this default approach:

    • Each record will have a different id, there will never be two entities with the same id;
    • Because of the sharing between all entities, the ids will grow much faster;
    • When used simultaneously by several applications, there will be some contention on the table, because it is being locked whenever the next high value is obtained and incremented;
    • The HIBERNATE_UNIQUE_KEY table is managed automatically by NHibernate (created, dropped and populated).

    One Row Per Entity

    Another option to consider, which is supported by NHibernate’s HiLo generator, consists of having each entity storing its next high value in a different row. You achieve this by supplying a where parameter to the generator:

       1: this.Id(x => x.SomeId, x =>
       2: {
       3:     x.Column("some_id");
       4:     x.Generator(Generators.HighLow, g => g.Params(new { where = "entity_type = 'some_entity'" }));
       5: });

    In it, you would specify a restriction on an additional column. The problem is, NHibernate knows nothing about this other column, so it won’t create it.

    One way to go around this is by using an auxiliary database object (maybe a topic for another post). This is a standard NHibernate functionality that allows registering SQL to be executed when the database schema is created, updated or dropped. Using mapping by code, it is applied like this:

       1: private static IAuxiliaryDatabaseObject OneHiLoRowPerEntityScript(Configuration cfg, String columnName, String columnValue)
       2: {
       3:     var dialect = Activator.CreateInstance(Type.GetType(cfg.GetProperty(NHibernate.Cfg.Environment.Dialect))) as Dialect;
       4:     var script = new StringBuilder();
       5:  
       6:     script.AppendFormat("ALTER TABLE {0} {1} {2} {3} NULL;\n{4}\nINSERT INTO {0} ({5}, {2}) VALUES (1, '{6}');\n{4}\n", TableHiLoGenerator.DefaultTableName, dialect.AddColumnString, columnName, dialect.GetTypeName(SqlTypeFactory.GetAnsiString(100)), (dialect.SupportsSqlBatches == true ? "GO" : String.Empty), TableHiLoGenerator.DefaultColumnName, columnValue);
       7:  
       8:     return (new SimpleAuxiliaryDatabaseObject(script.ToString(), null));
       9: }
      10:  
      11: Configuration cfg = ...;
      12: cfg.AddAuxiliaryDatabaseObject(OneHiLoRowPerEntityScript(cfg, "entity_type", "some_entity"));

    Keep in mind that this needs to go before the session factory is built. Basically, we are creating a SQL ALTER TABLE followed by an INSERT statement that change the default HiLo table and add another column that will serve as the discriminator. For making it cross-database, I used the registered Dialect class.

    Its schema will then look like this:

    image

    When NHibernate needs the next high value, this is what it does:

       1: -- select current value
       2: select next_hi
       3: from hibernate_unique_key with (updlock, rowlock)
       4: where entity_type = 'some_entity'
       5:  
       6: -- update current value
       7: update hibernate_unique_key
       8: set next_hi = @p0
       9: where next_hi = @p1
      10: and entity_type = 'some_entity';

    This approach only has advantages:

    • The HiLo table is still managed by NHibernate;
    • You have different id generators per entity (of course, you can still combine multiple entities under the same where clause), which will make them grow more slowly;
    • No contention occurs, because each entity is using its own record on the HIBERNATE_UNIQUE_KEY table.

    One Column Per Entity

    Yet another option is to have each entity using its own column for storing the high value. For that, we need to use the column parameter:

       1: this.Id(x => x.SomeId, x =>
       2: {
       3:     x.Column("some_id");
       4:     x.Generator(Generators.HighLow, g => g.Params(new { column = "some_column_id" }));
       5: });

    Like in the previous option, NHibernate does not know and therefore does not create this new column automatically. For that, we resort to another auxiliary database object:

       1: private static IAuxiliaryDatabaseObject OneHiLoColumnPerEntityScript(Configuration cfg, String columnName)
       2: {
       3:     var dialect = Activator.CreateInstance(Type.GetType(cfg.GetProperty(NHibernate.Cfg.Environment.Dialect))) as Dialect;
       4:     var script = new StringBuilder();
       5:  
       6:     script.AppendFormat("ALTER TABLE {0} {1} {2} {3} NULL;\n{4}\nUPDATE {0} SET {2} = 1;\n{4}\n", TableHiLoGenerator.DefaultTableName, dialect.AddColumnString, columnName, dialect.GetTypeName(SqlTypeFactory.Int32), (dialect.SupportsSqlBatches == true ? "GO" : String.Empty));
       7:  
       8:     return (new SimpleAuxiliaryDatabaseObject(script.ToString(), null));
       9: }
      10:  
      11: Configuration cfg = ...;
      12: cfg.AddAuxiliaryDatabaseObject(OneHiLoColumnPerEntityScript(cfg, "some_column_id"));

    The schema, with an additional column, would look like this:

    image

    And NHibernate executes this SQL for getting/updating the next high value:

       1: -- select current value
       2: select some_column_hi
       3: from hibernate_unique_key with (updlock, rowlock)
       4:  
       5: -- update current value
       6: update hibernate_unique_key
       7: set some_column_hi = @p0
       8: where some_column_hi = @p1;

    The only advantage in this model is to have separate ids per entity, contention on the HiLo table will still occur.

    One Table Per Entity

    The final option to consider is having a separate table per entity (or group of entities). For that, we use the table parameter:

       1: this.Id(x => x.SomeId, x =>
       2: {
       3:     x.Column("some_id");
       4:     x.Generator(Generators.HighLow, g => g.Params(new { table = "some_entity_unique_key" }));
       5: });

    In this case, NHibernate generates the new HiLo table for us, together with the default HIBERNATE_UNIQUE_KEY, if any entity uses it, with exactly the same schema:

    image

    And the SQL is, of course, also identical, except for the table name:

       1: -- select current value
       2: select next_hi
       3: from some_entity_unique_key with (updlock, rowlock)
       4:  
       5: -- update current value
       6: update some_entity_unique_key
       7: set next_hi = @p0
       8: where next_hi = @p1;

    Again, all pros and no cons:

    • Table still fully managed by NHibernate;
    • Different ids per entity or group of entities means they will grow slower;
    • Contention will only occur if more than one entity uses the same HiLo table.

    Conclusion

    As you can see, NHibernate is full of extensibility points. Even when it does not offer out of the box what we need, we usually have a way around it.

    Let me hear from you!

    Read more...

  • NHibernate Pitfalls: Aggregating Non-Nullable Numerical Values Without Records

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

    When you are performing a LINQ query that aggregates non-nullable numeric values – Sum, Average, for example – and the query does not return any values, you will get an exception. An example would be:

       1: var average = session.Query<Product>().Where(x => x.Price > 10000).Select(x => x.Price).Average();

    This is not specific to NHibernate, this behavior will be the same on LINQ to Objects as well, and it is caused by a null result trying to be assigned to a non-nullable variable.

    The workaround is simple, just cast the result property to an appropriate nullable equivalent:

       1: var average = session.Query<Product>().Where(x => x.Price > 10000).Select(x => (Decimal?) x.Price).Average();

    Read more...

  • NHibernate Connection Resiliency

    Entity Framework 6 included a feature known as connection resiliency. Basically, what it says is, when EF is trying to connect to a database, it will try a number of times before giving up. After each unsuccessful attempt, it will wait some time and then try again. As you can imagine, this is very useful, especially when we are dealing with cloud storage.

    NHibernate does not natively offer this, however, because it is highly extensible, it isn’t too hard to build one such mechanism, which is what I did.

    The code is below, as you can see, it consists of a custom implementation of DriverConnectionProvider, the component of NHibernate that opens connections for us.

       1: public class ResilientDriverConnectionProvider : DriverConnectionProvider
       2: {
       3:     public const String ConnectionDelayBetweenTries = "connection.delay_between_tries";
       4:     public const String ConnectionMaxTries = "connection.max_tries";
       5:  
       6:     private static readonly IInternalLogger log = LoggerProvider.LoggerFor(typeof(ResilientDriverConnectionProvider));
       7:  
       8:     public ResilientDriverConnectionProvider()
       9:     {
      10:         this.MaxTries = 3;
      11:         this.DelayBetweenTries = TimeSpan.FromSeconds(5);
      12:     }
      13:  
      14:     public Int32 MaxTries { get; set; }
      15:  
      16:     public TimeSpan DelayBetweenTries { get; set; }
      17:  
      18:     public override void Configure(IDictionary<String, String> settings)
      19:     {
      20:         String maxTries;
      21:         String delayBetweenTries;
      22:  
      23:         if (settings.TryGetValue(ConnectionMaxTries, out maxTries) == true)
      24:         {
      25:             this.MaxTries = Int32.Parse(maxTries);
      26:         }
      27:  
      28:         if (settings.TryGetValue(ConnectionDelayBetweenTries, out delayBetweenTries) == true)
      29:         {
      30:             this.DelayBetweenTries = TimeSpan.Parse(delayBetweenTries);
      31:         }
      32:  
      33:         base.Configure(settings);
      34:     }
      35:  
      36:     public override IDbConnection GetConnection()
      37:     {
      38:         IDbConnection con = null;
      39:  
      40:         for (var i = 0; i < this.MaxTries; ++i)
      41:         {
      42:             try
      43:             {
      44:                 log.Debug(String.Format("Attempting to get connection, {0} of {1}", (i + 1), this.MaxTries));
      45:                 con = base.GetConnection();
      46:                 log.Debug(String.Format("Got a connection after {0} tries", (i + 1)));
      47:  
      48:                 break;
      49:             }
      50:             catch(Exception ex)
      51:             {
      52:                 if (i == this.MaxTries - 1)
      53:                 {
      54:                     log.Error(String.Format("Could not get connection after {0} tries", this.MaxTries), ex);
      55:                     throw;
      56:                 }
      57:                 else
      58:                 {
      59:                     Thread.Sleep(this.DelayBetweenTries);
      60:                 }
      61:             }
      62:         }
      63:  
      64:         return (con);
      65:     }
      66: }

    The code wraps the attempt to open a connection and retries it a number of times, with some delay in between.

    The way to configure this, in fluent configuration, would be:

       1: var cfg = new Configuration()
       2:     .DataBaseIntegration(
       3:     db =>
       4:     {
       5:         db.ConnectionProvider<ResilientDriverConnectionProvider>();
       6:         //...
       7:     });

    Or if you prefer to use string properties, in either XML or fluent configuration, you can do:

       1: var cfg = new Configuration()
       2:     .SetProperty(NHibernate.Cfg.Environment.ConnectionProvider, typeof(ResilientDriverConnectionProvider).AssemblyQualifiedName);

    From looking at the class, you can see that it supports two properties:

    • MaxTries: the maximum number of connect attempts;
    • DelayBetweenTries: the amount of time to wait between two connection attempts.

    It is possible to supply this values by configuration:

       1: var cfg = new Configuration()
       2:     .SetProperty(NHibernate.Cfg.Environment.ConnectionProvider, typeof(ResilientDriverConnectionProvider).AssemblyQualifiedName)
       3:     .SetProperties(ResilientDriverConnectionProvider.ConnectionMaxTries, "3")
       4:     .SetProperties(ResilientDriverConnectionProvider.ConnectionDelayBetweenTries, TimeSpan.FromSeconds(5).ToString());

    As usual, hope you find this useful! Smile

    Read more...