Fluent Nhibernate In Memory DB testing with SQLite - Syntax Error calling user function in Select statement

Question

I'm trying to get our in memory database using SQLite and Nhibernate 4.0 and Fluent Nhibernate working for integration tests.

One of the entities has this mapping: Map(x => x.ShowNameLine2).Formula("FileTaxes.funcMultiplePayersExist(Id, TIN, User_Id)");

My problem is I'm getting a SQLite syntax error when running the query below (I've stripped out all the non-essential pieces):

SELECT 
  batch.Id, 
  FileTaxes.funcMultiplePayersExist(p.Id, p.TIN, p.User_Id) as formula 
FROM "Batch" batch
LEFT OUTER JOIN "BatchPayer" bp ON batch.Id = bp.Batch_id
LEFT OUTER JOIN "Payer" p ON bp.Payer_id = p.Id
----> System.Data.SQLite.SQLiteException : SQLite error near "(" : syntax error

I've tried defining a custom dialect and registering the function:

public class CustomSQLiteDialect : SQLiteDialect
{
    protected override void RegisterColumnTypes()
    {
        base.RegisterColumnTypes();
        ...
    }

    protected override void RegisterFunctions()
    {
        base.RegisterFunctions();
        ...
        RegisterFunction("FileTaxes.funcMultiplePayersExist", new StandardSQLFunction("FileTaxes.funcMultiplePayersExist", NHibernateUtil.Int32));
    }
}

We have defined our in memory configuration as below, but I still get the error when running my test using the in memory configuration and in memory session factory:

    private static NHibernate.Cfg.Configuration _configuration;

    public static FluentConfiguration InMemoryConfiguration()
    {
        if (_inMemoryConfigurationSingleton != null)
            return _inMemoryConfigurationSingleton;

        _inMemoryConfigurationSingleton =
            Fluently.Configure()
                .Mappings(m => m.FluentMappings.AddFromAssemblyOf<FormMap>().Conventions.AddFromAssemblyOf<Conventions.LengthAttributeConvention>())
                .Database(SQLiteConfiguration.Standard.InMemory().ShowSql().Driver<CustomSQLite20Driver>().Dialect<CustomSQLiteDialect>();)
                .ExposeConfiguration(config =>
                {
                    config.BeforeBindMapping += BeforeBindMappingHandler;
                    var schemaExport = new SchemaExport(config);
                    _configuration = config;
                    schemaExport.Create(true, true);
                });
        _inMemoryConfigurationSingleton.BuildConfiguration();

        return _inMemoryConfigurationSingleton;
    }

    public static ISessionFactory InMemorySessionFactory()
    {
        return _inMemorySessionFactorySingleton ?? (_inMemorySessionFactorySingleton = InMemoryConfiguration().BuildSessionFactory());
    }

Is there something I am missing to get this function to be registered during the in memory configuration setup? Does it have to do with the order the schema export is running?

Don't know if this helps stackoverflow.com/questions/4389672/… — David Osborne
I found that post before I asked the question. I still haven't been able to figure it out. When I set a breakpoint in the BeforeBindMapping handler it never gets called. The RegisterFunction method gets called in my customDialect class, but when I inspect the configuration afterwards it shows that no functions are registered. — NexAddo

Michael Hjorth Michael Hjorth · Accepted Answer · 2020-12-02T08:22:32

Probably the mappings are already built at the time you attach the eventhandler.

I had to build the configuration first and use it as argument to Fluently.Configure to make it work:

var cfg = new Configuration();
cfg.BeforeBindMapping += BeforeBindMappingHandler;
var sessionFactory = Fluently.Configure(cfg)
   .... 
;

Fluent Nhibernate In Memory DB testing with SQLite - Syntax Error calling user function in Select statement

1 Answers