I use an in-memory List<Person> AsQueryable to unit test this functionality , and in production swap the in-memory implementation with the NHQueryable API. The unit tests that run against the in- memory list fail. I'm trying to get around this problem.
On Jan 10, 6:11 pm, John Davidson <[email protected]> wrote: > For example: SQL: SELECT x = SUBSTRING('abcdef', 0, 3); RESULT: x = > 'ab' and NOT x = 'abc' > > Then try: SQL: SELECT x = SUBSTRING('abcdef', 0, 4); RESULT: x ='abc' > > Just add 1 to the number of chars to be returned, so that if you want 1 then > use (0, 2) > > John Davidson > > On Mon, Jan 10, 2011 at 8:08 PM, Vikram Nayak <[email protected]>wrote: > > > 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.aspxSUBSTRING > > (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]<nhusers%[email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. -- 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.
