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"));
}
}
}
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();
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 ?
On Jan 10, 2:24 pm, Vikram Nayak <[email protected]> wrote:
> 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
> SUBSTRINGhttp://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.
