NHibernate LINQ Contains doesn't work

I am new to NHibernate and working with FluentNhibernate for a new project. I came accross a strange issue when executing a very simple linq query.

Query 1 - Works very well

      //Query 1
          var customers = from customer in _session.Query<Customer>() 
                        where customer.FirstName == "john"
                        select new
                        {
                            customer.FirstName,
                            customer.LastName
                        };

When below query 2 is executed I get an error "Could not execute query"

       //Query 2.
           var customers = from customer in _session.Query<Customer>() 
                         where customer.FirstName.Contains("john")
                        select new
                        {
                            customer.FirstName,
                            customer.LastName
                        };

The NH generated query is,

select customer0_.FirstName as col_0_0_, customer0_.LastName as col_1_0_ from tblCustomer customer0_ where customer0_.FirstName li开发者_如何学运维ke ('%'||@p0||'%');@p0 = 'john'

it has generated FirstName like ('%'||@p0||'%') instead of FirstName like ('%'+@p0+'%') as I can understand.

The database I am using is SQLServerCE and the NHibernate version is 3.1.0

Please help resolving this issue.

This is the definition of concat function in "Dialect.cs" file (base dialect class of NH):

RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "||", ")"));

Which should be

RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));

for SQL CE. So you have to use MsSqlCe40Dialect class. I think it's not registered in the fluent-NHibernate yet, so you can define it this way:

... MsSqlCeConfiguration.Standard  
                      ...  
                      .Dialect<MsSqlCe40Dialect>();  

Also there is a patch for the rest of missing functions, waiting for approval: https://nhibernate.jira.com/browse/NH-2723 Please vote!

In the meantime you can create your own dialect:

using NHibernate;
using NHibernate.Dialect;
using NHibernate.Dialect.Function;

namespace Test1
{
    public class TempSqlCeDialect : MsSqlCeDialect
    {
        public TempSqlCeDialect()
        {
            RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
        }
    }
}

And then you can use it by defining:

... MsSqlCeConfiguration.Standard.Dialect<TempSqlCeDialect>() ...