Showing posts with label NHibernate. Show all posts
Showing posts with label NHibernate. Show all posts

Tuesday, December 12, 2023

Fix Linq 2 NHibernate for MySQL

If you use NHibernate with MySQL and Linq 2 NHibernate to simplify fetching data you may face with problem: queries created by Linq2NH use square brackets by default. That is fine for SQL Server but won't work in MySQL which uses backticks ``.

For MySQL we need to instruct NHibernate to use backticks instead of square brackets. It can be done by setting interceptor in NH config:

public class NHConfiguration
{
    public static Configuration Build(string connStr)
    {
        var config = Fluently.Configure()
            .Database(
                MySQLConfiguration.Standard
                    .ConnectionString(connStr)
                    .AdoNetBatchSize(100)
                    .DoNot.ShowSql()
            )
            .Mappings(cfg =>
            {
                // add mappings
            })
            .ExposeConfiguration(x =>
            {
with backticks for MySQL
                x.SetInterceptor(new ReplaceBracesWithBackticksInterceptor());
            });

        return config.BuildConfiguration();
    }
}

in its OnPrepareStatement method we just replace square brackets with backticks:

public class ReplaceBracesWithBackticksInterceptor : EmptyInterceptor
{
    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        return sql.Replace("[", "`").Replace("]", "`");
    }
}

After that Linq2NH will start working in MySQL.

Tuesday, July 4, 2023

Generate database schema for MySQL using NHibernate hbm2ddl tool

NHibernate has hbm2ddl tool which allows automatically export database tables schema based on provided mappings. I.e. we may define mapping using C# for some POCO class:

public class User
{
    public virtual int Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

like that:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("[User]");
        Id(x => x.Id, "UserId");
        Map(x => x.FirstName);
        Map(x => x.LastName);
     }
}

and then based on this mapping hbm2ddl will generate the following SQL code for creating User table:

create table `User` (
    UserId INTEGER NOT NULL AUTO_INCREMENT,
    FirstName TEXT,
    LastName TEXT,
    primary key (UserId)
);

That is convenient because we don't need to maintain database schema separately - any change in C# models and mappings will be automatically reflected in db schema. However in order to use hbm2ddl for MySQL we need to add few tweaks to the export code:

  • MySQL uses back ticks instead of square brackets
  • MySQL syntax requires semicolon after each code line in generated SQL

First of all we need to create Fluent NHibernate configuration for MySQL syntax:

var config = Fluently.Configure()
    .Database(
        MySQLConfiguration.Standard
            .ConnectionString(connectionString)
            .AdoNetBatchSize(100)
            .DoNot.ShowSql()
    )
    .Mappings(cfg =>
    {
        cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
            .Conventions.Setup(mappings =>
            {
                mappings.AddAssembly(typeof(UserMap).Assembly);
            });
    });

With this Fluent NHibernate config export code will look like this:

var nhConfig = ... // see above
var export = new SchemaExport(nhConfig);
var sb = new StringBuilder();
export.Create(schema =>
{
    schema = schema.Replace("[", "`").Replace("]", "`");
    if (schema.EndsWith("\r\n"))
    {
        schema = schema.Substring(0, schema.Length - 2) + ";\r\n";
    }
    else
    {
        schema += ";";
    }
    sb.Append(schema);
}, false);

Console.WriteLine(sb.ToString());

Here we replace square brackets by back ticks and add semicolon to the end of each added line. After that we will have valid SQL code for MySQL syntax with database schema.

Saturday, June 17, 2023

StackOverflowException when use hierarchical object mapping with AutoMapper and Fluent NHibernate lazy loading

Suppose that we have the following class Category with self-reference (regular parent-child hierarchy):

public class Category
{
    public virtual T Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Category ParentCategory { get; set; }
    public virtual IList<Category> ChildCategories { get; set; }
}

(properties are virtual which is required by NHibernate). Regular Fluent NHibernate mapping for this class looks like this:

public class CategoryMap : ClassMap<Category>
{
    public CategoryMap()
    {
        Table("[Category]");
        Id(x => x.Id);
        
        Map(x => x.Name);

        References(x => x.ParentCategory)
            .Column("ParentCategoryId");
        
        HasMany(x => x.ChildCategories)
            .KeyColumn("ParentCategoryId")
            .Inverse()
            .AsBag();
    }
}

Then we want to show category in the view of ASP.Net Core MVC app and use the following view model for that:

public class CategoryModel
{
    public int? Id { get; set; }

    public string Name { get; set; }

    public string ParentCategoryName { get; set; }
}

For mapping Category to CategoryModel the following AutoMapper mapping is used:

public class ViewModelProfile : Profile
{
    public ViewModelProfile()
    {
        CreateMap<Category, CategoryModel>()
            .ForMember(x => x.ParentCategoryName, o => o.MapFrom(c => c.ParentCategory == null ? "" : c.ParentCategory.Name))
    }
}

i.e. when ParentCategory is null then ParentCategoryName property of view model is empty string, otherwise ParentCategoryName will return name of parent category. Until now everything looks good. Problems will start when we will try to create category of nesting level which is greater than 1, i.e. this will work:

child category -> parent category

but with deeper hierarchy:

child category1 -> child category2 -> parent category

we will get StackOverflowException when will try to pass view model (created by AutoMapper) to the view. Googling this problem suggests to use MaxDepth(2) call when create AotuMapper map profile to limit number of processed hierarchy levels but it didn't help because of some reason.

Solution which worked was to specify to not use lazy loading for ParentCategory property in Fluent NHibernate mapping (NHibernate uses lazy loading by default):

public class CategoryMap : ClassMap<Category>
{
    public CategoryMap()
    {
        Table("[Category]");
        Id(x => x.Id);
        
        Map(x => x.Name);

        References(x => x.ParentCategory)
            .Column("ParentCategoryId")
            .Not.LazyLoad();
        
        HasMany(x => x.ChildCategories)
            .KeyColumn("ParentCategoryId")
            .Inverse()
            .AsBag();
    }
}

In theory it may cause performance problems with large amount of data and deep hierarchy levels but with reasonable amount of data should work quite well.

Tuesday, March 27, 2012

Using NHibernate and related libraries in commercial projects

I like open source and try to take part in OSS ecosystem by supporting several own projects and contributing patches and fixes in projects which I used in my work. However if you live in enterprise development world you should care about licensing questions and before to decide which library or component you will use for proprietary software we should check does it contain compatible license.

Some time ago I made investigated licensing of NHibernate and it’s related projects which reveal all power of this ORM and make it more effective and productive: Fluent NHibernate and Linq 2 NHibernate. I decided to put this information into my blog because I know how licensing issues may be boring for developers and this info may also be useful for other people.

First of all about versioning. At the moment when I checked it (end of 2011) the following versions were available:

Fluent NHibernate 1.1 depends on NHibernate 2.1.2.4000
Linq 2 NHibernate 1.0.0.4000 -> NHibernate 2.1.0.4000
Linq 2 NHibernate 1.1.0.1001 -> NHibernate 2.1.2.4000

(There was lack of support of NHibernate 3.x in related projects). You may use other versions and use assembly binding redirection of course. In the table above I showed versions which were used for compilation of particular projects, i.e. if you will use them – you won’t have problems with compatibility. So it is possible e.g. to use NHibernate 2.1.2.4000, Fluent NHibernate 1.1 and Linq 2 NHibernate 1.1.0.1001 in the project without additional configurations. Now about licensing.

Library License
NHibernate GNU Lesser General Public License. This license allows to use library both in OSS and in commercial software.
Fluent NHibernate BSD license. It also allows to use it in commercial software. Plus according to the license text from project repository you should provide its license.txt with your application.

Linq 2 NHibernate

It is part of NHibernate contrib project which is also under LGPL, which allows to use it in commercial software.

So as summary you may use NHibernate and 2 mentioned projects in commercial software, except the cases when there are special conditions from the customers which may prevent to use that.

Tuesday, December 6, 2011

Fix problem in Fluent NHibernate Search with custom field bridges

NHibernate search is the project from NHibernate contrib which allows you to integrate NHibernate and NLucene search. The basic idea of NHibernate search is similar to the NHibernate at common: if NHibernate allows you to map entities to the database tables, then NHibernate search allows to map entities to NLucene index documents (if you are not familiar with NLucene basic concepts, I recommend you book Lucene in action which is written for Java, but all described concepts are also suitable for .Net). Briefly NLucene index is document database which contains documents and each document has several fields.

Fluent NHibernate library simplifies mapping configuration: it allows you to write mappings on C# instead of xml which is used in NHibernate. Similarly Fluent NHibernate search allows you to write mappings from your POCO to the NLucene index documents (the same POCO which are used for mapping to the database tables via Fluent NHibernate). Let’s consider example – model for ecommerce application.

Suppose that we have Product class:

   1: public class Product
   2: {
   3:     public virtual int Id { get; set; }
   4:     public virtual string Name { get; set; }
   5:     public virtual IList<Category> Categories { get; set; }
   6: }

Each product may belong to several categories. Each category may have parent category and several child categories:

   1: public class Category
   2: {
   3:     public virtual int Id { get; set; }
   4:     public virtual string Name { get; set; }
   5:     public virtual Category ParentCategory { get; set; }
   6:     public virtual IList<Category> ChildCategories { get; set; }
   7: }

We want to search by product names. Mapping will look like this:

   1: public class ProductMap : DocumentMap<Product>
   2: {
   3:     protected override void Configure()
   4:     {
   5:         Id(x => x.Id);
   6:         Name("Product");
   7:  
   8:         Map(x => x.Name)
   9:             .Store().Yes()
  10:             .Index().Tokenized();
  11:     }
  12: }

Here we specified map from Product class to the NLucene document. Store().Yes() means that index will store value of “Name” as is (so users will be able to search by whole name), Index().Tokenized() means that we want to tokenize name so users will be able to search by parts of the product name (full text search. Need to note here that for different languages it can be achieved by using different analyzers, but it is subject for another articles).

Everything is clear here. But then we need to add possibility for users to filter search by specific categories. In order to do this we need to store categories ids to the index somehow (add new field to the Product document). As you saw Product.Categories property has IList<Category>. The problem is that NLucene works with strings. So we need to convert IList<Category> to the string value. It can be done using custom field bridge. NHibernate search contains number of built-in bridges for dates, guids, strings. But in our case we need to write our own bridge – inheritor of IFieldBridge. It will retrieve all categories ids and concatenate them in string field:

   1: public class CategoriesToStringBridge : IFieldBridge
   2: {
   3:     public void Set(string name, object value, Document document,
   4:         Field.Store store, Field.Index index, float? boost)
   5:     {
   6:         var categories = value as IEnumerable<Category>;
   7:         if (categories == null)
   8:         {
   9:             return;
  10:         }
  11:  
  12:         var ids = new List<int>();
  13:         foreach (var category in categories)
  14:         {
  15:             ids.Add(category.Id);
  16:  
  17:             var cat = category.ParentCategory;
  18:             while (cat != null)
  19:             {
  20:                 ids.Add(cat.Id);
  21:                 cat = cat.ParentCategory;
  22:             }
  23:         }
  24:  
  25:         string val = string.Join(",", ids.Distinct());
  26:         var field = new Field(name, val, store, index);
  27:         if (boost.HasValue)
  28:         {
  29:             field.SetBoost(boost.Value);
  30:         }
  31:         document.Add(field);
  32:     }
  33: }

Note that for each category of the current product we also add all parent categories (lines 12-23). So if users will search by parent category id they will also see products from all child categories. On the lines 25-31 we create new field and add it to the document.

Now we need to configure search mapping. Fluent NHibernate search allows to do it like this:

   1: public class ProductMap : DocumentMap<Product>
   2: {
   3:     protected override void Configure()
   4:     {
   5:         Id(x => x.Id);
   6:         Name("Product");
   7:  
   8:         Map(x => x.Name)
   9:             .Store().Yes()
  10:             .Index().Tokenized();
  11:  
  12:         Map(x => x.Categories)
  13:             .Store().Yes()
  14:             .Bridge().Custom<CategoriesToStringBridge>()
  15:             .Index().UnTokenized();
  16:     }
  17: }

We don’t want to analyze Categories field – so we specified Index().UnTokenized(). However when you will compile and run the program, you will get the following exception:

NHibernate.HibernateException: Unable to guess IFieldBridge for Categories
  at NHibernate.Search.Bridge.BridgeFactory.GuessType(String fieldName, Type fieldType, IFieldBridgeDefinition fieldBridgeDefinition, IDateBridgeDefinition dateBridgeDefinition)
  at FluentNHibernate.Search.Mapping.Parts.FluentMappingPart..ctor(PropertyInfo propertyInfo)
  at FluentNHibernate.Search.Mapping.Parts.FieldMappingPart..ctor(PropertyInfo propertyInfo)
  at FluentNHibernate.Search.Mapping.DocumentMap`1.Map(Expression`1 property)

Exception occurs in FluentNHibernate.Search.Mapping.Parts.FluentMappingPart constructor:

   1: protected FluentMappingPart(PropertyInfo propertyInfo)
   2: {
   3:     this.Name(propertyInfo.Name);
   4:  
   5:     // set the default getter
   6:     this.Getter = new BasicPropertyAccessor.BasicGetter(propertyInfo.DeclaringType,
   7:         propertyInfo, propertyInfo.Name);
   8:  
   9:     // set the default bridge
  10:     var bridge = BridgeFactory.GuessType(propertyInfo.Name, propertyInfo.PropertyType,
  11:         null, null);
  12:     (this as IHasBridge).FieldBridge = bridge;
  13: }

On the line 10 it calls BridgeFactory.GuessType() method. Note that 3rd parameter (fieldBridgeDefinition) definition is null here, and as we have property of IList<Categories>, NHibernate search can’t find appropriate bridge at this moment and throws exception. As it shown in the search map configuration Bridge().Custom<CategoriesToStringBridge>() is called after Map() method:

   1: Map(x => x.Categories)
   2:     .Store().Yes()
   3:     .Bridge().Custom<CategoriesToStringBridge>()
   4:     .Index().UnTokenized();

So when FluentMappingPart constructor is called it doesn’t know yet about custom bridge.

In order to fix it, we can use the following simple workaround: enclose call to BridgeFactory.GuessType() with try/catch block:

   1: protected FluentMappingPart(PropertyInfo propertyInfo)
   2: {
   3:     this.Name(propertyInfo.Name);
   4:  
   5:     // set the default getter
   6:     this.Getter = new BasicPropertyAccessor.BasicGetter(propertyInfo.DeclaringType,
   7:         propertyInfo, propertyInfo.Name);
   8:  
   9:     // asadomov: need to add try/catch here - otherwise it fails 
  10:     // for the values with custom bridge (e.g. Product.Categories)
  11:     try
  12:     {
  13:         // set the default bridge
  14:         var bridge = BridgeFactory.GuessType(propertyInfo.Name, propertyInfo.PropertyType,
  15:             null, null);
  16:         (this as IHasBridge).FieldBridge = bridge;
  17:     }
  18:     catch
  19:     {
  20:     }
  21: }

Now the exception won’t be re-thrown and our custom bridge will be successfully applied for the Categories properties. When you will re-build your search index – you can use e.g. Luke tool (you need to install Java to use it. However it works with index built by NLucene) to check that Product documents contain Categories field which contains categories ids, separated by comma. So you will be able to filter search results by categories (how to do it is the subject for another post).

Wednesday, July 27, 2011

Configure NH in code instead of hibernate.cfg.xml

In one of my ASP.Net MVC projects xml file was used for NH configuration (hibernate.cfg.xml) because of historical reasons. It was annoying because settings were stored in several files: web.config and hibernate.cfg.xml. It was double annoying because Fluen NHibernate contains API for configuration (in my project Fluent NH was used as well). But as it often happens I had no time to fix it. Configuration code looked like this:

   1: public class ConfigurationFactory
   2: {
   3:     public Configuration Build(string configurationFile)
   4:     {
   5:         var configuration = new Configuration();
   6:  
   7:         if (string.IsNullOrEmpty(configurationFile))
   8:             configuration.Configure();
   9:         else
  10:             configuration.Configure(configurationFile);
  11:  
  12:         return Fluently.Configure(configuration)
  13:             .Mappings(cfg =>
  14:             {
  15:                 cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
  16:                     .Conventions.Setup(mappings =>
  17:                         {
  18:                             mappings.AddAssembly(typeof(UserMap).Assembly);
  19:                             mappings.Add(ForeignKey.EndsWith("Id"));
  20:                         });
  21:             }).BuildConfiguration();
  22:     }
  23: }

And configuration file is the following:

   1: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
   2:   <session-factory>
   3:     <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
   4:     <property name="connection.connection_string">...</property>
   5:     <property name="show_sql">false</property>
   6:     <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
   7:     <property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider,NHibernate.Caches.SysCache</property>
   8:     <property name="cache.use_query_cache">true</property>
   9:     <property name="adonet.batch_size">100</property>
  10:      <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
  11:   </session-factory>
  12: </hibernate-configuration>

Recently I had time to fix it and publish it here for quick reference:

   1: public class ConfigurationFactory
   2: {
   3:     public Configuration Build(string connectionString)
   4:     {
   5:         if (string.IsNullOrEmpty(connectionString))
   6:         {
   7:             connectionString =
   8:                 ConfigurationManager.ConnectionStrings["connectionStringName"].ConnectionString;
   9:         }
  10:  
  11:         return Fluently.Configure()
  12:             .Database(
  13:                 MsSqlConfiguration.MsSql2005
  14:                     .Cache(c => c
  15:                         .UseQueryCache()
  16:                         .ProviderClass<SysCacheProvider>())
  17:                     .ConnectionString(connectionString)
  18:                     .AdoNetBatchSize(100)
  19:                     .DoNot.ShowSql()
  20:                     .ProxyFactoryFactory(typeof(ProxyFactoryFactory))
  21:             )
  22:             .Mappings(cfg =>
  23:             {
  24:                 cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
  25:                     .Conventions.Setup(mappings =>
  26:                         {
  27:                             mappings.AddAssembly(typeof(UserMap).Assembly);
  28:                             mappings.Add(ForeignKey.EndsWith("Id"));
  29:                         });
  30:             }).BuildConfiguration();
  31:     }
  32: }

Here we read connection string from web.config and do the rest actions via Fluent configuration API. After that hibernate.cfg.xml can be finally removed from the project.