replace your in-memory list with nh using sqlite. it's a little bit more configuration but the results are more accurate. creating a base test class to configure the in-memory database is all it would really take. chances are the problem is case sensitivity.
On Jan 11, 2:55 pm, Vikram Nayak <[email protected]> wrote: > 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 > > ... > > read more » -- 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.
