There appears to be mismatch between the SQL generated by NHibernate
and SQL expected by SQL2008 in the following case:
[Test]
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 lastNameCriterion = new
LastNameCriterion() { LastNameLetters
= "B" };
var persons =
queryable.Where(lastNameCriterion.WherePredicate()).ToList();
transaction.Commit();
Assert.That(persons.Count,
Is.EqualTo(1)); //Expect to return
Person = "Foo Bar"; but returns Empty list
}
}
}
public class LastNameCriterion
{
public virtual string LastNameLetters { get; set; }
public Expression<Func<Person, bool>> WherePredicate()
{
string[] lastNames = LastNameLetters.Split(',');
return r =>
lastNames.Contains(r.LastName.Substring(0,
1).ToUpper()); // PROBLEM IN THIS LINE OF CODE ???
}
}
public class Person
{
public virtual long Id { get; set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
}
public class PersonMap : ClassMap<Person>
{
public PersonMap()
{
Id(p => p.Id);
Map(p => p.FirstName);
Map(p => p.LastName);
}
}
The resulting sql query generated by NHibernate for
var persons =
queryable.Where(lastNameCriterion.WherePredicate()).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 */)
But 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 ?
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
