Linq to Nhibernate with Contains and SubString does not work?

There appears to be mismatch betweeen the SQL generated by NHibernate and SQL expected by SQL2008 in the following case:

    public void PersistPerson()
    {
        var sessionFactory = CreateSessionFactory();
        using (var session = sessionFactory.OpenSession())
        {
            using(var transaction = session.BeginTransaction())
            {
                session.Save(new Person {FirstName = "Foo", LastName = "Bar"});
                session.Save(new Person {FirstName = "Foo", 开发者_开发知识库LastName = "Dah"});
                session.Save(new Person {FirstName = "Foo", LastName = "Wah"});
                transaction.Commit();
            }
        }
        using (var session = sessionFactory.OpenSession())
        {
            using(var transaction = session.BeginTransaction())
            {
                var queryable = from p in session.Query<Person>() select p;
                var lastNames = new[]{"B", "D"};
                var result = queryable.Where(r => lastNames.Contains(r.LastName.Substring(0, 1))).ToList();
                transaction.Commit();

                Assert.That(result[0].LastName, Is.EqualTo("Bar"));
            }
        }
    }

The resulting sql query generated by NHibernate for

var result = queryable.Where(r => lastNames.Contains(r.LastName.Substring(0, 1))).ToList();

is:

select person0_.Id        as Id0_,
   person0_.FirstName as FirstName0_,
   person0_.LastName  as LastName0_ from   [Person] person0_ where upper(substring(person0_.LastName,
                   0 /* @p0 */,
                   1 /* @p1 */)) in ('B' /* @p2 */)

From the MSDN documentation for T-SQL SUBSTRING http://msdn.microsoft.com/en-us/library/ms187748.aspx SUBSTRING (value_expression ,start_expression ,length_expression )

although the documentation says otherwise, from the comments posted start_expression appears to be 1 - based (not 0 indexed)

For example: SQL: SELECT x = SUBSTRING('abcdef', 0, 3); RESULT: x = 'ab' and NOT x = 'abc'

Any thoughts on how I can get around this ?

I think it's a bug. just change your codes to r.LastName.Substring(1, 1) and it works (resulting sql will be substring(1,1)).