NHibernate YesNo on SQL-Server CE No mapping exists from DbType AnsiStringFixedLength

Question

Trying to use nhibernate on sql-server compact edition for unit testing and having some trouble. At the moment I just have one entity which is:

 <class name="Audit" table="eolaudit_llk">
<id name="ID" column="eolauditlk_ky">
  <generator class="identity"></generator>
</id>
<property name="Name" column="eolauditlk_nm" />
<property name="Description" column="eolauditlk_dn" />
<property name="Active" column="active_fg" type="YesNo" />
<property name="UpdateUser" column="update_user_id" />
<property name="UpdateDateTime" column="update_dm"/>

I'm pretty new to nhibernate. I'm using the following code to build the session:

        public static ISessionFactory CrashAndBurnCompactSQLSessionFactory(string ConnectionString)
    {
        return Fluently.Configure()
            .Database(MsSqlCeConfiguration.Standard
                .ConnectionString(ConnectionString)
                .ShowSql()
                .UseOuterJoin()
                .QuerySubstitutions("true 1, false 0, yes 'Y', no 'N'")
                .Dialect("NHibernate.Dialect.MsSqlCeDialect")
                )
            .Mappings(m =>
            {
                m.HbmMappings
                  .AddFromAssemblyOf<Audit>();
            })
            .ExposeConfiguration(BuildSchema)
            .BuildSessionFactory();
    }

It works fine with SQL-Server, but when I use the SQL-Server CE session from above I get the following error:

    failed: System.ArgumentException: No mapping exists from DbType AnsiStringFixedLength to a known SqlDbType. at System.Data.SqlServerCe.SqlCeType.FromDbType(DbType type)
 at System.Data.SqlServerCe.SqlCeParameter.set_DbType(DbType value)

This is a head-melter - I don't want to do my testing on sql-server, and can't change the db. It's definitley related that the handling of that YesNo type which maps from a 'Y' or'N' char(1) db field to a boolean in the object.

As I said, the thing works fine in SQL-Server.

Answer 1

SQL Server CE doesn't support DbType.AnsiStringFixedLength (see here). Unfortunately YesNo derives from this DbType.

You could write your own YesNo custom type, or use type Char (it maps to DbType.StringFixedLength, which is supported by both versions of SQL Server). Neither option is particularly appealing. I know you said this isn't an option, but...if you are targeting SQL Server, it might be worth re-evaluating your strategy of testing on CE. Why not SQL Server Express, for example?